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

sql-server, xml

Solution

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

Problem

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' ```

Original source