How to GROUP BY XML coloumn

• lexcerm

  SSC Veteran

  Points: 221

  More actions

  July 18, 2018 at 3:14 am

  #354694

  First post here so hi!

  I have used some XML in my SQL to split a string and this works fine.  

  When trying to GROUP BY after adding a calculation, I get the following error message:-
  "Column 'm.n' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

  I have added all the columns in the select one-by-one, but it keeps bombing out on this one.

  My snippets of code are:-
  TA.COST / COUNT(DISTINCT(WO.FIELD1))

  LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)')))

  CAST('<XMLRoot><RowData>' + REPLACE(
  (CASE WHEN WO.FIELD1 IS NULL THEN 'UK' ELSE WO.FIELD1 END),';','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x

  CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

  I have tried 'n' and 'm.n' but receive the following message:-
  "The column 'n' that was returned from the nodes() method cannot be used directly. It can only be used with one of the four XML data type methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks."

  Many thanks in advance and regards,
  Lex.

• sgmunson

  SSC Guru

  Points: 110651

  More actions

  July 19, 2018 at 2:51 pm

  #1998311

  Need the entire query, not just snippets.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  July 19, 2018 at 3:16 pm

  #1998317

  You can't group by an XML column.  The best that you can do is convert it to a (N)(VAR)CHAR column, which you can group by.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  July 19, 2018 at 10:27 pm

  #1998328

  lexcerm - Wednesday, July 18, 2018 3:14 AM

  First post here so hi!

  I have used some XML in my SQL to split a string and this works fine.  

  When trying to GROUP BY after adding a calculation, I get the following error message:-
  "Column 'm.n' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

  I have added all the columns in the select one-by-one, but it keeps bombing out on this one.

  My snippets of code are:-
  TA.COST / COUNT(DISTINCT(WO.FIELD1))

  LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)')))

  CAST('<XMLRoot><RowData>' + REPLACE(
  (CASE WHEN WO.FIELD1 IS NULL THEN 'UK' ELSE WO.FIELD1 END),';','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x

  CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

  I have tried 'n' and 'm.n' but receive the following message:-
  "The column 'n' that was returned from the nodes() method cannot be used directly. It can only be used with one of the four XML data type methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks."

  Many thanks in advance and regards,
  Lex.

  Hi and welcome.
  Can I ask you to please post a full example of the XML, any DDL (create table) scripts if applicable, the full query and the expected results?
  😎
  Without the complete knowledge of the problem, even the simplest of problems can be hard to solve.