Les methods XML du server SQL ne sont pas autorisées dans une clause GROUP BY

Je viens d'émettre un groupe par où je spécifie des données xml alors j'ai eu erreur Les methods XML ne sont pas autorisés dans une clause GROUP BY.

voici mon 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 

Si je ne peux pas spécifier datatables XML dans le groupe par CLUASE alors quelle autre option est disponible …. s'il vous plaît guider. Merci

De cette façon, je réalise ma tâche

 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' 

Vous pouvez utiliser une table dérivée et faire le groupe par dans la requête principale.

 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