Query XML column and get Group by a XML field value

• IMary

  Old Hand

  Points: 361

  More actions

  October 27, 2019 at 6:13 am

  #3692182

  Hi,

  I have table Table A with 3 columns. One of these 3 columns, is an XML  column.

  Col 1: id

  Col 2: Businessday

  Col3: Payload (XML column)

  I have a XML field  root/deptid inside XML payload.

  Now, I need get count (id), businessday, deptid, from Table A group by deptid.

  Select  count(id) , Businessday,  Payload.value(/Root/:deptid)[1]', 'nvarchar(MAX)') as deptNumber

  from Table A

  where cast(businessday as date)='2019-10-10'

  group by businessday, deptnumber

  I'm getting error that I can not group by on XML column.

  Please advise how can I group by with deptNumber?

   

   

   

  • This topic was modified 5 years ago by  IMary.

• DesNorton

  SSC-Insane

  Points: 24386

  More actions

  October 27, 2019 at 6:45 am

  #3692184

  Try shredding your xml in a CTE, then do the count/group by using the CTE as source

  • This reply was modified 5 years ago by  DesNorton.

  ---

  How to post data/code on a forum to get the best help.