Reputation: 26406
Given an xml file in a recursive/hierarchical format like this:
taxonomy.xml
<taxonomy>
<record code="B">
<name>Basic Needs</name>
<facet>Service</facet>
<record code="BD">
<name>Food</name>
<facet>Service</facet>
<record code="BD-1800">
<name>Emergency Food</name>
<facet>Service</facet>
<record code="BD-1800.1000">
<name>Brown Bag Food Programs</name>
<facet>Service</facet>
</record>
<record code="BD-1800.1500">
<name>Commodity Supplemental Food Program</name>
<facet>Named Programs</facet>
</record>
<record code="BD-1800.1900">
<name>Food Lines</name>
<facet>Service</facet>
</record>
...
</record>
...
</record>
...
</record>
<record code="D">
<name>Consumer Services</name>
<facet>Service</facet>
...
</record>
...
</taxonomy>
Is there a way to use T-SQL to load and flatten the structure into a tabular format like this:
For example, the desired result for the XML above would be:
I've written a little console application to do this after failing to come up with the correct SQL but I can't help thinking there's an easier way.
Here's a SQL Fiddle with the xml: http://sqlfiddle.com/#!6/9eecb7/3265/0
Upvotes: 3
Views: 1089
Reputation: 857
;WITH xmlCTE
AS
(
SELECT
record.query('./*') query,
record.value('@code', 'varchar(50)') code,
record.value('name[1]', 'varchar(50)') name,
record.value('facet[1]', 'varchar(50)') facet,
0 depth
FROM @taxonomy.nodes('/taxonomy/record') t(record)
UNION ALL
SELECT
records.query('./*') query,
records.value('@code', 'varchar(50)') code,
records.value('name[1]', 'varchar(50)') name,
records.value('facet[1]', 'varchar(50)') facet,
depth + 1
FROM xmlCTE
CROSS APPLY query.nodes('./record') t2(records)
)
SELECT code, name, facet, depth
FROM xmlCTE
working SQL Fiddle: http://sqlfiddle.com/#!6/9eecb7/3269/0
Upvotes: 2