title: Sql server XML methods are not allowed in a GROUP BY clause
body: <p>i just issue a group by where i specify xml data then i got error <strong>XML methods are not allowed in a GROUP BY clause.</strong></p>
  
  <p>here is my sql </p>
  
  <pre><code>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
  </code></pre>
  
  <p>if i can not specify xml data in group by cluase then what other option is available....please guide. thanks</p>
  
  <h2>This way i achieve my task</h2>
  
  <pre><code>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)&gt;=@StartDate AND
      Convert(Varchar,ModDate,112)&lt;=@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)&gt;=@StartDate AND
      Convert(Varchar,LogDate,112)&lt;=@EndDate
  
  )
  ORDER BY ID,RowID DESC
  
  --sp_HourSheetLog '20140101','20140326'
  </code></pre>