sql-server - 如何使用 T-SQL 展平递归/分层 XML 文件

Question

给定一个递归/分层格式的 xml 文件，如下所示:

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>

有没有一种方法可以使用 T-SQL 将结构加载并展平为表格格式，如下所示:

• 代码
• 姓名
• 方面
• 深度(层次结构的深度)

例如，上述 XML 的预期结果为:

在未能提出正确的 SQL 后，我编写了一个小的控制台应用程序来执行此操作，但我不禁想到有更简单的方法。

这是带有 xml 的 SQL Fiddle: http://sqlfiddle.com/#!6/9eecb7/3265/0

Answer 1

;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

工作 SQL fiddle :http://sqlfiddle.com/#!6/9eecb7/3269/0