Extract data from XML field

Question

Post reply

Extract data from XML field

SqlRookie

SSC Enthusiast

Points: 198
More actions
October 21, 2021 at 2:30 pm
Go to Answer
#3942867

Hello,
I have a SQL view with a column called GlobalCountryRegionXML.
Here is an example of a value stored in this field:-
<GlobalCountryRegion>
<CountryRegion>
<Country>United Kingdom</Country>
<Region>UK-IE</Region>
</CountryRegion>
<CountryRegion>
<Country>United States</Country>
<Region>NORAM</Region>
</CountryRegion>
</GlobalCountryRegion>
From this, I would like two further columns showing the following results (in the same single row) separated by a comma:-
Country: United Kingdom, United States
Region: UK-IE, NORAM
I've tried so many variations of code using NODES and CROSS APPLY, but it just isn't happening! lol
Many thanks in advance.

Mark Cowne

One Orange Chip

Points: 26952

October 21, 2021 at 4:42 pm

Answer

#3942935

This should work pre 2016

select stuff((select ',' + n.x.value('Country[1]','varchar(30)') as "text()"
from MyView t
cross apply t.GlobalCountryRegionXML.nodes('/GlobalCountryRegion/CountryRegion') n(x) 
order by row_number() over(order by n.x)
for xml path('')),1,1,'') as Country,
stuff((select ',' + n.x.value('Region[1]','varchar(30)') as "text()"
from MyView t
cross apply t.GlobalCountryRegionXML.nodes('/GlobalCountryRegion/CountryRegion') n(x) 
order by row_number() over(order by n.x)
for xml path('')),1,1,'') as Region

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 7 posts - 1 through 6 (of 6 total)

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

with cte as (
select n.x.value('Country[1]','varchar(30)') as Country,
       n.x.value('Region[1]','varchar(30)') as Region,
   row_number() over(order by n.x) as rn
from MyView t
cross apply t.GlobalCountryRegionXML.nodes('/GlobalCountryRegion/CountryRegion') n(x)
)
select string_agg(Country, ', ') within group (order by rn) as Country,
       string_agg(Region, ', ') within group (order by rn) as Region
from cte;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

SqlRookie SSC Enthusiast Points: 198 More actions · Answer 2

Many thanks for the reply Mark.

I am getting the following error with the STRING_AGG function:-

"Msg 195, Level 15, State 10, Line 8

'string_agg' is not a recognized built-in function name."

I have read that this may not be available in 2016 and have tried STRING_ADD (which was one suggestion I researched but didn't work) and have read XML PATH and STUFF might be a way round it, but I am no expert.

Many thanks again.

SqlRookie SSC Enthusiast Points: 198 More actions · Answer 3

Good morning Mark,

Thank you so much for your assistance with this. It works a treat!

However, there is one final part to this. When applying the code to my overall view, I need to add column REF_NO to split the information over the rest of the records (so each record will have a different COUNTRY/REGION etc).

I've tried to add something like ROW_NUMBER() OVER (PARTITION BY REF_NO) in several different places, but no joy.

Can you please advise on this?

Many thanks again!

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

Not totally clear to me what you're asking for, maybe this?

with cte as (
select REF_NO,
       n.x.value('Country[1]','varchar(30)') as Country,
       n.x.value('Region[1]','varchar(30)') as Region,
       row_number() over(order by n.x) as rn
from MyView t
cross apply t.GlobalCountryRegionXML.nodes('/GlobalCountryRegion/CountryRegion') n(x)
)
select t1.REF_NO,
stuff((select ',' + t2.Country as "text()"
       from cte t2
   where t2.REF_NO = t1.REF_NO
   order by t2.rn
   for xml path('')),1,1,'') as Country,
stuff((select ',' + t2.Region as "text()"
       from cte t2
   where t2.REF_NO = t1.REF_NO
   order by t2.rn
   for xml path('')),1,1,'') as Region
from cte t1
group by t1.REF_NO
order by t1.REF_NO;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

SqlRookie SSC Enthusiast Points: 198 More actions · Answer 5

Yes my fault, I should have compiled my original question better, my apologies.

So for each row of data there is this XML column and a REF_NO column. Each REF_NO will have it's own unique XML based on Country/Region. At the moment, your original code gives me all XML values in this column for each record, rather than for each unique REF_NO.

So for example, I am looking for something like:-

1234...FRANCE, ITALY.....EMEA, EMEA

1235...UK, SPAIN.....UK-IE, EMEA

Your revised code runs for a while but produces NULL values.

Thanks for your help, it has given me a great insight and good footing to see if I can resolve this.

Many thanks.