Sql server XML methods are not allowed in a GROUP BY clause

Question

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.

here is my 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

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

This way i achieve my task

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'

Answer 1

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