如何基于SQL表数据通过XSLT生成XML树形结构？

问题背景 #

现有如下结构的SQL表及数据：

需要将这些数据转换为指定的树形XML结构，预期输出：

<A>
  <B>
    <C>
      <D/>
    </C>
    <E>
      <F/>
      <G/>
    </E>
  </B>
</A>

以下是基于XSLT的完整解决方案：

步骤1：从SQL导出规范化的原始XML数据 #

先用SQL的FOR XML PATH生成结构规整的原始XML，清理各Level字段中的横线以提取纯节点名称：

SELECT 
    -- 清理Level字段中的横线，得到纯节点名
    LTRIM(RTRIM(REPLACE([Level 1], '-', ''))) AS Level1,
    LTRIM(RTRIM(REPLACE([Level 2], '-', ''))) AS Level2,
    LTRIM(RTRIM(REPLACE([Level 3], '-', ''))) AS Level3,
    LTRIM(RTRIM(REPLACE([Level 4], '-', ''))) AS Level4,
    LEAF
FROM YourTableName -- 替换为你的实际表名
FOR XML PATH('Node'), ROOT('Nodes')

执行后会生成如下结构的原始XML：

<Nodes>
  <Node>
    <Level1>A</Level1>
    <Level2>B</Level2>
    <Level3>C</Level3>
    <Level4></Level4>
    <LEAF>N</LEAF>
  </Node>
  <Node>
    <Level1>A</Level1>
    <Level2>B</Level2>
    <Level3>C</Level3>
    <Level4>D</Level4>
    <LEAF>Y</LEAF>
  </Node>
  <Node>
    <Level1>A</Level1>
    <Level2>B</Level2>
    <Level3>E</Level3>
    <Level4>F</Level4>
    <LEAF>Y</LEAF>
  </Node>
  <Node>
    <Level1>A</Level1>
    <Level2>B</Level2>
    <Level3>E</Level3>
    <Level4>G</Level4>
    <LEAF>Y</LEAF>
  </Node>
</Nodes>

步骤2：编写XSLT转换模板 #

创建如下XSLT文件，用于将原始XML转换为树形结构：

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" indent="yes"/>

  <!-- 匹配根节点，启动Level1节点处理 -->
  <xsl:template match="/Nodes">
    <xsl:apply-templates select="Node[Level1 != ''][not(preceding-sibling::Node/Level1 = current()/Level1)]" mode="level1"/>
  </xsl:template>

  <!-- 生成Level1节点，并处理下属Level2节点 -->
  <xsl:template match="Node" mode="level1">
    <xsl:element name="{Level1}">
      <xsl:apply-templates select="../Node[Level1 = current()/Level1 and Level2 != ''][not(preceding-sibling::Node[Level1 = current()/Level1]/Level2 = current()/Level2)]" mode="level2"/>
    </xsl:element>
  </xsl:template>

  <!-- 生成Level2节点，并处理下属Level3节点 -->
  <xsl:template match="Node" mode="level2">
    <xsl:element name="{Level2}">
      <xsl:apply-templates select="../Node[Level1 = current()/Level1 and Level2 = current()/Level2 and Level3 != ''][not(preceding-sibling::Node[Level1 = current()/Level1 and Level2 = current()/Level2]/Level3 = current()/Level3)]" mode="level3"/>
    </xsl:element>
  </xsl:template>

  <!-- 生成Level3节点，并处理下属Level4叶子节点 -->
  <xsl:template match="Node" mode="level3">
    <xsl:element name="{Level3}">
      <xsl:apply-templates select="../Node[Level1 = current()/Level1 and Level2 = current()/Level2 and Level3 = current()/Level3 and Level4 != '']" mode="level4"/>
    </xsl:element>
  </xsl:template>

  <!-- 生成Level4叶子节点 -->
  <xsl:template match="Node" mode="level4">
    <xsl:element name="{Level4}"/>
  </xsl:template>

  <!-- 忽略非叶子的中间节点（如A-B-C这条记录） -->
  <xsl:template match="Node[LEAF='N' and Level4='']"/>
</xsl:stylesheet>

XSLT逻辑说明：

• 按层级递归处理，每个层级自动去重相同节点
• 过滤掉LEAF='N'的中间节点（这类节点是叶子节点的父节点，无需单独生成）
• 最终生成嵌套的树形XML结构

步骤3：在SSIS中执行转换 #

1. 拖入XML源组件，配置为读取SQL生成的原始XML数据
2. 添加XSLT转换组件，指定上述XSLT文件路径
3. 添加XML目标组件，设置输出路径为目标.xml文件
4. 运行SSIS包即可得到预期的树形XML文件

内容的提问来源于stack exchange，提问作者Anurag K.