GROUP BY 子句中不允许使用 Sql 服务器 XML 方法 [英] Sql server XML methods are not allowed in a GROUP BY clause

问题描述

我只是通过在其中指定 xml 数据的位置发出一个组，然后出现错误 在 GROUP BY 子句中不允许使用 XML 方法.

i just issue a group by where i specify xml data then i got error XML methods are not allowed in a GROUP BY clause.

这是我的 sql

SELECT HourSheetID,(MAX(RowID)+1) as "RowID",
XMLData.value('(Log/EntryDate)[1]','datetime') as EntryDate,
XMLData.value('(Log/SpecialistID)[1]','int') as SpecialistID,
XMLData.value('(Log/HoursData)[1]','decimal(18,2)') as HoursData,
XMLData.value('(Log/UpdatedBy)[1]','varchar(max)') as UpdatedBy,
XMLData.value('(Log/Options)[1]','varchar(max)') as Options,
logdate
FROM  dbo.EditedHourSheetLog
GROUP BY HourSheetID,
XMLData.value('(Log/EntryDate)[1]','datetime'),
XMLData.value('(Log/SpecialistID)[1]','int'),
XMLData.value('(Log/HoursData)[1]','decimal(18,2)'),
XMLData.value('(Log/UpdatedBy)[1]','varchar(max)'),
XMLData.value('(Log/Options)[1]','varchar(max)'),
logdate

如果我不能按条款指定组中的 xml 数据，那么还有什么其他选项可用....请指导.谢谢

if i can not specify xml data in group by cluase then what other option is available....please guide. thanks

ALTER PROC sp_HourSheetLog
(
    @StartDate VARCHAR(8),
    @EndDate VARCHAR(8)
)
AS

SELECT B.ID
    ,A.RowID
    ,B.EntryDate
    ,B.Name
    ,B.HoursData
    ,B.UpdatedBy
    ,Options=(CASE B.Options
    WHEN 'rdLeave' THEN 'Leave'
    WHEN 'rdsick' THEN 'Sick'
    WHEN 'rdSalvage' THEN 'Salvage'
    WHEN 'rdCSRDuty' THEN 'CSR Duty'
    WHEN 'rdShippingSales' THEN 'Shipping and Sales'
    WHEN 'rdEOL' THEN 'EOL'
    WHEN 'rdTraining' THEN 'Training'
    WHEN 'rdOther' THEN 'Other'
    END)
    ,B.ModDate
        FROM (
        (
            SELECT HourSheetID,(MAX(RowID)+1) as "RowID"
            FROM EditedHourSheetLog l,EditedHourSheet h
            GROUP BY HourSheetID
        ) A
        JOIN
        (
            SELECT h.ID
                ,s.Name
                ,h.EntryDate
                ,h.HoursData
                ,h.Options 
                ,h.UpdatedBy
                ,h.ModDate from EditedHourSheet h
                LEFT JOIN Specialists s
                ON h.SpecialistID=s.SpecialistID
        ) B
    ON A.HourSheetID=B.ID
    )
    WHERE Convert(Varchar,ModDate,112)>=@StartDate AND
    Convert(Varchar,ModDate,112)<=@EndDate


UNION 
(
    Select HourSheetID as ID,RowID,
    XMLData.value('(Log/EntryDate)[1]','datetime') as EntryDate,
    --XMLData.value('(Log/SpecialistID)[1]','int') as SpecialistID,
    s.Name,
    XMLData.value('(Log/HoursData)[1]','decimal(18,2)') as HoursData,
    CAST(XMLData.value('(Log/UpdatedBy)[1]','varchar(max)') AS VARCHAR(MAX)) UpdatedBy,
    Options=(CASE XMLData.value('(Log/Options)[1]','varchar(max)')
    WHEN 'rdLeave' THEN 'Leave'
    WHEN 'rdsick' THEN 'Sick'
    WHEN 'rdSalvage' THEN 'Salvage'
    WHEN 'rdCSRDuty' THEN 'CSR Duty'
    WHEN 'rdShippingSales' THEN 'Shipping and Sales'
    WHEN 'rdEOL' THEN 'EOL'
    WHEN 'rdTraining' THEN 'Training'
    WHEN 'rdOther' THEN 'Other'
    END),
    LogDate as ModDate
    FROM EditedHourSheetLog h
    LEFT JOIN Specialists s
    ON h.XMLData.value('(Log/SpecialistID)[1]','int')=s.SpecialistID
    WHERE Convert(Varchar,LogDate,112)>=@StartDate AND
    Convert(Varchar,LogDate,112)<=@EndDate

)
ORDER BY ID,RowID DESC

--sp_HourSheetLog '20140101','20140326'

推荐答案

您可以使用派生表并在主查询中进行分组.

You can use a derived table and do the group by in the main query.

SELECT T.HourSheetID,
       MAX(T.RowID)+1 as RowID,
       T.EntryDate,
       T.SpecialistID,
       T.HoursData,
       T.UpdatedBy,
       T.Options,
       T.logdate
FROM (
     SELECT HourSheetID,
            RowID,
            XMLData.value('(Log/EntryDate)[1]','datetime') as EntryDate,
            XMLData.value('(Log/SpecialistID)[1]','int') as SpecialistID,
            XMLData.value('(Log/HoursData)[1]','decimal(18,2)') as HoursData,
            XMLData.value('(Log/UpdatedBy)[1]','varchar(max)') as UpdatedBy,
            XMLData.value('(Log/Options)[1]','varchar(max)') as Options,
            logdate
     FROM  dbo.EditedHourSheetLog
     ) AS T
GROUP BY T.HourSheetID,
         T.EntryDate,
         T.SpecialistID,
         T.HoursData,
         T.UpdatedBy,
         T.Options,
         T.logdate

这篇关于GROUP BY 子句中不允许使用 Sql 服务器 XML 方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！