Jeremy Danyow
Jeremy Danyow

Reputation: 26406

How to flatten recursive/hierarchical XML file with T-SQL

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:

table

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

Answers (1)

George T
George T

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

Related Questions