How to GROUP BY XML column

Question

How to GROUP BY XML column

lexcerm

SSC Veteran

Points: 221
More actions
July 18, 2018 at 6:47 am

#414391

I think I posted this in the wrong forum originally (GENERAL) so here goes again. Take two! 🙂
--------------------------------------
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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004649 More actions · Answer 1

lexcerm - Wednesday, July 18, 2018 6:47 AM
I have added all the columns in the select one-by-one, but it keeps bombing out on this one.

If it's just one string, then you're probably good to go. If it's many strings that are contained in a table, then the XML method is definitely NOT the way to go. It's comparatively horribly slow.

How many strings are you trying to split and how wide are they?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lexcerm SSC Veteran Points: 221 More actions · Answer 2

Hi Jeff,

Thank you for replying. I have actually researched some of your excellent work, but haven't been able to adapt this for my situation.

The string I am wanting to split will always have a semi-colon separator, so something like this:-
AA;BB;CC;DD;EE;FF;GG etc.
This string could potentially be split up to, say, 100 times or beyond.

As I am working with SQL 2012, I do not have the luxury of being able to use the new 2016 STRING_SPLIT function.

Thanks again! 🙂

sgmunson SSC Guru Points: 110651 More actions · Answer 3

lexcerm - Thursday, July 19, 2018 2:11 AM
Hi Jeff,
Thank you for replying. I have actually researched some of your excellent work, but haven't been able to adapt this for my situation.
The string I am wanting to split will always have a semi-colon separator, so something like this:-
AA;BB;CC;DD;EE;FF;GG etc.
This string could potentially be split up to, say, 100 times or beyond.
As I am working with SQL 2012, I do not have the luxury of being able to use the new 2016 STRING_SPLIT function.
Thanks again! 🙂

Jeff's string splitting function is excellent at this task. You can get it at the end of the following artice: http://www.sqlservercentral.com/articles/72993/

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

Jeff Moden SSC Guru Points: 1004649 More actions · Answer 4

lexcerm - Thursday, July 19, 2018 2:11 AM
Hi Jeff,
Thank you for replying. I have actually researched some of your excellent work, but haven't been able to adapt this for my situation.
The string I am wanting to split will always have a semi-colon separator, so something like this:-
AA;BB;CC;DD;EE;FF;GG etc.
This string could potentially be split up to, say, 100 times or beyond.
As I am working with SQL 2012, I do not have the luxury of being able to use the new 2016 STRING_SPLIT function.
Thanks again! 🙂

Did Steve's good answer help you or are you still stuck with using an XML splitter?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)