Extract data from XML field
-
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.
-
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
-
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.
-
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 -
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!
-
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 -
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.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply