T-SQL中如何从存储XML的列提取值生成逗号分隔字符串列表

你的表中ResultDoc列为nvarchar类型存储XML内容，可通过XQuery节点解析配合字符串聚合实现批量查询，无需游标或循环处理。

实现代码 #

首先将代码中YourTableName替换为你实际的表名即可直接执行：

-- 兼容性写法，支持所有SQL Server 2008及以上版本
SELECT
    t.Name,
    STUFF(
        (
            SELECT ', ' + node.value('.', 'nvarchar(max)')
            FROM (SELECT CAST(t.ResultDoc AS XML) AS XmlData) xmlConv
            CROSS APPLY xmlConv.XmlData.nodes('/doc/results/result[not(@type="pending")]') AS resultNodes(node)
            FOR XML PATH(''), TYPE
        ).value('.', 'nvarchar(max)'),
        1, 2, ''
    ) AS Results
FROM YourTableName t;

如果你确认环境是SQL Server 2017及以上（包含2019），可以用更简洁的STRING_AGG写法：

-- SQL Server 2017+ 简洁写法
SELECT
    t.Name,
    STRING_AGG(resultNodes.node.value('.', 'nvarchar(max)'), ', ') AS Results
FROM YourTableName t
CROSS APPLY (SELECT CAST(t.ResultDoc AS XML) AS XmlData) xmlConv
CROSS APPLY xmlConv.XmlData.nodes('/doc/results/result[not(@type="pending")]') AS resultNodes(node)
GROUP BY t.Name;

逻辑说明 #

• 首先将nvarchar类型的ResultDoc显式转换为SQL Server原生XML类型，否则无法调用XQuery解析方法
• XPath路径/doc/results/result[not(@type="pending")]会直接过滤掉携带type="pending"属性的result节点，仅返回符合要求的节点集合
• 逐行解析时通过CROSS APPLY关联当前行的XML内容，天然支持多行批量处理，不需要额外编写单行处理的包装逻辑
• 字符串拼接部分，第一种写法通过FOR XML PATH生成带固定前缀的拼接串，再用STUFF移除开头多余的分隔符；第二种直接用内置聚合函数STRING_AGG实现拼接，代码更简洁

执行结果 #

基于你提供的示例数据，最终返回结果如下：

注意：如果你的XML内容声明了默认命名空间，需要在查询最开头添加WITH XMLNAMESPACES声明对应命名空间前缀，否则XPath无法匹配到节点。示例数据无命名空间，不需要额外配置。

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