TSQL动态解析XML：无需新增字段适配多choice节点的方法

针对这种包含动态多choice节点的XML提取场景，完全可以避免每次新增节点就手动修改SQL的重复操作，下面提供两种实用方案：

1. 批量拆分节点（CROSS APPLY + nodes()方法） #

通过nodes()方法将field下的所有choice节点拆分为独立行，批量提取值，后续新增choice节点无需修改SQL。

假设你的XML结构示例：

<auditElement>
  <field name="偏好选项">
    <choice value="选项A" />
    <choice value="选项B" />
    <choice value="选项C" />
  </field>
</auditElement>

对应的TSQL查询：

SELECT 
    -- 可选：提取field的名称
    fieldNode.value('@name', 'nvarchar(100)') AS FieldName,
    -- 提取每个choice的value值
    choiceNode.value('@value', 'nvarchar(100)') AS ChoiceValue
FROM 
    -- 假设表名为AuditRecords，XML字段名为AuditContent
    AuditRecords
CROSS APPLY 
    AuditContent.nodes('/auditElement/field') AS Field(fieldNode)
CROSS APPLY 
    fieldNode.nodes('choice') AS Choice(choiceNode)

如果需要将多个choice值合并为单个字段（比如逗号分隔），可结合STRING_AGG函数：

SELECT 
    fieldNode.value('@name', 'nvarchar(100)') AS FieldName,
    STRING_AGG(choiceNode.value('@value', 'nvarchar(100)'), ', ') AS AllChoices
FROM 
    AuditRecords
CROSS APPLY 
    AuditContent.nodes('/auditElement/field') AS Field(fieldNode)
CROSS APPLY 
    fieldNode.nodes('choice') AS Choice(choiceNode)
GROUP BY 
    fieldNode.value('@name', 'nvarchar(100)')

2. 动态SQL生成（适用于列展示场景） #

如果必须将每个choice值作为单独列展示（如Choice1、Choice2...），可以用动态SQL自动生成提取语句，无需手动新增字段。

示例代码：

DECLARE @targetXml XML = (SELECT AuditContent FROM AuditRecords WHERE RecordId = 1);
DECLARE @sql NVARCHAR(MAX) = '';
DECLARE @choiceCount INT = @targetXml.value('count(/auditElement/field/choice)', 'INT');

-- 循环生成每个choice的列提取语句
WHILE @choiceCount > 0
BEGIN
    SET @sql += ', Choice' + CAST(@choiceCount AS NVARCHAR(10)) + ' = @targetXml.value(''/auditElement/field/choice[' + CAST(@choiceCount AS NVARCHAR(10)) + ']/@value'', ''nvarchar(100)'')'
    SET @choiceCount -= 1
END

-- 拼接并执行完整SQL
SET @sql = 'SELECT ' + STUFF(@sql, 1, 2, '') + ' FROM (SELECT @targetXml AS XmlData) AS T'
EXEC sp_executesql @sql, N'@targetXml XML', @targetXml = @targetXml;

方案对比 #

• 第一种行拆分方案更符合关系型数据库范式，扩展和维护成本更低，是优先推荐的方式。
• 第二种动态SQL方案适合特定的报表展示场景，注意使用参数化避免SQL注入风险。

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