SQL Server 2016按列生成指定格式类XML输出的高效方法

针对大量列的场景，无需逐列编写查询，可以通过动态SQL+XML命名空间统一声明的方式实现需求，同时移除<repeat>节点的xmlns:xsi命名空间，保留空值的xsi:nil标记。

实现思路 #

1. 从系统视图动态提取目标表的列名（排除无需处理的ID列）
2. 拼接动态SQL语句，为每个列生成对应<Answer>节点的XML片段
3. 在最外层统一声明xsi命名空间，避免子节点重复输出命名空间属性
4. 执行动态SQL生成最终符合要求的XML

完整代码示例 #

-- DDL和示例数据初始化，开始
DECLARE @tbl TABLE 
             ( 
                 ID INT IDENTITY PRIMARY KEY, 
                 FirstName VARCHAR(20), 
                 MiddleName VARCHAR(20), 
                 LastName VARCHAR(20)
             );

INSERT @tbl (FirstName, MiddleName, LastName) 
VALUES ('Fred', 'A.','Smith'),
       ('Anna', NULL,'Polack');
-- DDL和示例数据初始化，结束

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)

-- 动态获取需要处理的列名（排除ID列）
SELECT @cols = STRING_AGG(
    CONCAT(
        '<Answer name="', QUOTENAME(c.name, '&quot;'), '">',
        '(SELECT ', QUOTENAME(c.name), ' AS value FROM @tbl ORDER BY ID FOR XML PATH(''''), ROOT(''repeat''), ELEMENTS XSINIL)',
        '</Answer>'
    ), ' + '
)
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'tbl' AND c.name != 'ID'

-- 构建动态SQL，外层统一声明xsi命名空间
SET @sql = CONCAT(
    'DECLARE @result XML;',
    'SET @result = (SELECT ', @cols, ' FOR XML PATH(''''), ROOT(''Answers''), XMLNAMESPACES(''http://www.w3.org/2001/XMLSchema-instance'' AS xsi));',
    'SELECT @result.query(''/Answers/node()'') AS FinalXML;'
)

-- 执行动态SQL，传递表变量确保数据安全
EXEC sp_executesql @sql, N'@tbl TABLE (ID INT, FirstName VARCHAR(20), MiddleName VARCHAR(20), LastName VARCHAR(20))', @tbl = @tbl

输出结果 #

<Answer name=&quot;FirstName&quot;>
  <repeat>
    <value>Fred</value>
    <value>Anna</value>
  </repeat>
</Answer>
<Answer name=&quot;MiddleName&quot;>
  <repeat>
    <value>A.</value>
    <value xsi:nil=&quot;true&quot; />
  </repeat>
</Answer>
<Answer name=&quot;LastName&quot;>
  <repeat>
    <value>Smith</value>
    <value>Polack</value>
  </repeat>
</Answer>

关键说明 #

• 动态列适配：通过系统视图自动识别列名，无需手动维护列列表，适配表结构变化
• 命名空间优化：外层统一声明xsi命名空间后，内部<repeat>节点不再重复输出xmlns:xsi属性，同时空值的xsi:nil标记完整保留
• 性能与安全：使用STRING_AGG拼接列逻辑（SQL Server 2017+支持，2016可替换为FOR XML PATH拼接），通过sp_executesql传递参数避免SQL注入风险

内容的提问来源于stack exchange，提问作者AA23ds