December 4, 2014 at 11:20 am
I have a XML and need to process it in SQL, either using openxml or xquery. I need to return the xml hierarchy, together with the data content. I have no problem returning the data content, but I'm at odds on how to get the hierarchy. Any help is appreciated and for that I have the following example XML:
declare @xml XML = convert(xml,'
<Root>
<Country>
<Name>USA</Name>
<City Name="Mpls">
<Population>
<No>500,000</No>
</Population>
</City>
<City Name="St Paul">
<Population>
<No>250,000</No>
</Population>
</City>
</Country>
<Country>
<Name>Canada</Name>
<City Name="Toronto">
<Population>
<No>2,000,000</No>
</Population>
</City>
<City Name="Montreal">
<Population>
<No>1,500,000</No>
</Population>
</City>
</Country>
<Country>
<Name>USA</Name>
<City Name="NYC">
<Population>
<No>6,000,000</No>
</Population>
</City>
<City Name="St Paul">
<Population>
<No>300,000</No>
</Population>
</City>
</Country>
</Root>')
The query I have doesn't return the hierarchy and looks like this:
selectCountry = CTR.value('Name[1]','varchar(6)'),
City = C.value ('@Name[1]','varchar(8)'),
Population = C.value('Population[1]/No[1]','varchar(10)')
from@xml.nodes('Root/Country') as a(CTR) cross apply
CTR.nodes('City') as x(C)
The result I'm looking for looks like this:
CountryID CityID Country City Population
----------- ----------- ------- -------- ----------
1 1 USA Mpls 500,000
1 2 USA St Paul 250,000
2 2 Canada Toronto 2,000,000
2 2 Canada Montreal 1,500,000
3 1 USA NYC 6,000,000
3 2 USA St Paul 300,000
December 4, 2014 at 12:32 pm
Here's a partial solution:
selectCountryId = ctr.value('for $i in . return count(../*[. << $i]) + 1', 'int'),
CityId = c.value('for $i in . return count(../*[. << $i]) + 1', 'int'),
Country = CTR.value('Name[1]','varchar(6)'),
City = C.value ('@Name[1]','varchar(8)'),
Population = C.value('Population[1]/No[1]','varchar(10)')
from@xml.nodes('Root/Country') as a(CTR) cross apply
CTR.nodes('City') as x(C)
Here's a good explanation:
http://stackoverflow.com/questions/1134075/finding-node-order-in-xml-document-in-sql-server
Gerald Britton, Pluralsight courses
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply