怎么在SQL中用FOR XML PATH生成复杂的嵌套XML

在SQL Server的业务场景中，经常需要将查询结果转换为符合特定格式的XML数据，FOR XML PATH是实现这一需求的核心功能，它支持灵活的XML结构自定义，能够满足从简单到复杂的各类XML生成需求，尤其是嵌套XML的构建，通过合理的查询设计就能高效实现。

FOR XML PATH基础用法回顾

FOR XML PATH的基本作用是将查询结果集的行转换为XML元素，默认情况下，每一行会生成一个父节点，列名会作为子节点名称。我们可以通过PATH参数指定父节点的名称，也可以通过列别名控制生成的XML结构。

先看一个简单的示例，生成最基础的XML结果：

-- 创建测试表并插入数据
CREATE TABLE TestUser (
    UserId INT,
    UserName NVARCHAR(50),
    Age INT
)
INSERT INTO TestUser VALUES (1, '张三', 25), (2, '李四', 30)

-- 基础FOR XML PATH查询
SELECT UserId, UserName, Age FROM TestUser FOR XML PATH('User'), ROOT('Users')

上述查询会生成如下结构的XML：

<Users>
  <User>
    <UserId>1</UserId>
    <UserName>张三</UserName>
    <Age>25</Age>
  </User>
  <User>
    <UserId>2</UserId>
    <UserName>李四</UserName>
    <Age>30</Age>
  </User>
</Users>

构建单层嵌套XML的方法

如果需要在XML中生成子节点包含多个子元素的嵌套结构，可以通过给列设置别名，使用斜杠分隔路径的方式实现。比如我们需要在User节点下增加一个Address子节点，Address节点下包含City和Street两个子节点。

示例代码如下：

-- 新增地址测试数据
ALTER TABLE TestUser ADD City NVARCHAR(50), Street NVARCHAR(100)
UPDATE TestUser SET City='北京', Street='朝阳路1号' WHERE UserId=1
UPDATE TestUser SET City='上海', Street='南京路2号' WHERE UserId=2

-- 生成带单层嵌套的XML
SELECT 
    UserId,
    UserName,
    Age,
    City AS 'Address/City',
    Street AS 'Address/Street'
FROM TestUser 
FOR XML PATH('User'), ROOT('Users')

生成的XML结构如下，Address节点就是嵌套在User节点下的子结构：

<Users>
  <User>
    <UserId>1</UserId>
    <UserName>张三</UserName>
    <Age>25</Age>
    <Address>
      <City>北京</City>
      <Street>朝阳路1号</Street>
    </Address>
  </User>
  <User>
    <UserId>2</UserId>
    <UserName>李四</UserName>
    <Age>30</Age>
    <Address>
      <City>上海</City>
      <Street>南京路2号</Street>
    </Address>
  </User>
</Users>

构建多层嵌套XML的技巧

当嵌套结构更复杂，比如一个用户对应多个订单，每个订单又包含多个商品时，就需要使用子查询结合FOR XML PATH来实现多层嵌套。核心思路是在主查询的列中，嵌入一个返回子XML结构的子查询，子查询的FOR XML PATH结果会作为父节点的一个子元素。

首先创建订单和商品的测试表：

-- 创建订单表
CREATE TABLE TestOrder (
    OrderId INT,
    UserId INT,
    OrderTime DATETIME
)
INSERT INTO TestOrder VALUES (1001, 1, '2024-01-01'), (1002, 1, '2024-01-02'), (1003, 2, '2024-01-03')

-- 创建订单商品表
CREATE TABLE TestOrderGoods (
    OrderId INT,
    GoodsName NVARCHAR(50),
    Price DECIMAL(10,2)
)
INSERT INTO TestOrderGoods VALUES (1001, '手机', 3999.00), (1001, '耳机', 299.00), (1002, '笔记本', 6999.00), (1003, '平板', 4999.00)

接下来编写查询，生成用户下包含多个订单，每个订单下包含多个商品的多层嵌套XML：

SELECT 
    u.UserId,
    u.UserName,
    u.Age,
    -- 子查询生成订单嵌套结构
    (
        SELECT 
            o.OrderId,
            o.OrderTime,
            -- 子查询生成订单商品嵌套结构
            (
                SELECT 
                    g.GoodsName,
                    g.Price
                FROM TestOrderGoods g
                WHERE g.OrderId = o.OrderId
                FOR XML PATH('Goods'), TYPE
            ) AS 'GoodsList'
        FROM TestOrder o
        WHERE o.UserId = u.UserId
        FOR XML PATH('Order'), TYPE
    ) AS 'OrderList'
FROM TestUser u
FOR XML PATH('User'), ROOT('Users')

这里需要注意两个关键点：一是子查询的FOR XML PATH后面要加TYPE关键字，这样子查询返回的是XML类型的结果，而不是NVARCHAR类型，才能正确嵌套到父XML中；二是子查询的别名要和父节点中需要的子节点名称对应。

上述查询生成的XML结构如下：

<Users>
  <User>
    <UserId>1</UserId>
    <UserName>张三</UserName>
    <Age>25</Age>
    <OrderList>
      <Order>
        <OrderId>1001</OrderId>
        <OrderTime>2024-01-01T00:00:00</OrderTime>
        <GoodsList>
          <Goods>
            <GoodsName>手机</GoodsName>
            <Price>3999.00</Price>
          </Goods>
          <Goods>
            <GoodsName>耳机</GoodsName>
            <Price>299.00</Price>
          </Goods>
        </GoodsList>
      </Order>
      <Order>
        <OrderId>1002</OrderId>
        <OrderTime>2024-01-02T00:00:00</OrderTime>
        <GoodsList>
          <Goods>
            <GoodsName>笔记本</GoodsName>
            <Price>6999.00</Price>
          </Goods>
        </GoodsList>
      </Order>
    </OrderList>
  </User>
  <User>
    <UserId>2</UserId>
    <UserName>李四</UserName>
    <Age>30</Age>
    <OrderList>
      <Order>
        <OrderId>1003</OrderId>
        <OrderTime>2024-01-03T00:00:00</OrderTime>
        <GoodsList>
          <Goods>
            <GoodsName>平板</GoodsName>
            <Price>4999.00</Price>
          </Goods>
        </GoodsList>
      </Order>
    </OrderList>
  </User>
</Users>

生成带属性的XML技巧

如果需要生成的XML节点包含属性，而不是子元素，可以通过给列设置别名，用@开头的方式定义属性名。比如我们需要把UserId作为User节点的属性，而不是子元素。

示例代码如下：

SELECT 
    UserId AS '@UserId',  -- @开头表示作为父节点的属性
    UserName,
    Age
FROM TestUser 
FOR XML PATH('User'), ROOT('Users')

生成的XML中UserId会成为User节点的属性：

<Users>
  <User UserId="1">
    <UserName>张三</UserName>
    <Age>25</Age>
  </User>
  <User UserId="2">
    <UserName>李四</UserName>
    <Age>30</Age>
  </User>
</Users>

处理特殊字符的注意事项

当数据中包含XML的特殊字符比如<、>、&时，FOR XML PATH会自动对这些字符进行转义，不需要我们手动处理。比如UserName中包含特殊字符，查询结果会自动转换：

-- 插入包含特殊字符的测试数据
INSERT INTO TestUser VALUES (3, '王五&a', 28, '广州', '天河路3号')

-- 查询包含特殊字符的数据
SELECT UserId, UserName, Age FROM TestUser WHERE UserId=3 FOR XML PATH('User'), ROOT('Users')

生成的XML中&会被转义为&，保证XML格式的正确性：

<Users>
  <User>
    <UserId>3</UserId>
    <UserName>王五&amp;a</UserName>
    <Age>28</Age>
  </User>
</Users>

常见问题汇总

• 子查询嵌套后XML格式不正确：检查子查询的FOR XML PATH后是否加了TYPE关键字，没有加的话子查询返回的是字符串，会被当作文本内容而不是XML节点。
• 嵌套节点名称不符合预期：检查子查询的别名是否和父节点中需要的子节点名称一致，路径拼接的斜杠是否正确。
• 生成的XML有多余的命名空间：如果需要去除默认的命名空间，可以在ROOT后加上XMLSCHEMA相关的参数调整，或者在查询中显式处理命名空间。

SQLFOR_XML_PATHXML嵌套XML修改时间：2026-06-14 17:54:35