如何在SQL查询结果的XML字段中提取所有<value>标签内的内容

你可以直接使用SQL Server内置的XML节点查询方法提取所有<value>标签内容，以下是两种常用场景的实现代码： #

场景1：每个value值单独占一行 #

如果需要把每个的内容拆分为独立行，使用以下语句：

SELECT
    c.name,
    v.value('.', 'VARCHAR(MAX)') AS ExtractedValue
FROM
    client c
    CROSS APPLY (SELECT convert(xml, convert(varbinary(max), c.orders)) AS OrderXml) x
    CROSS APPLY x.OrderXml.nodes('//value') AS T(v)

代码说明 #

• //value 是XPath表达式，表示匹配XML内容中所有层级的标签
• nodes() 方法会把匹配到的每个节点拆分为独立行
• value('.', 'VARCHAR(MAX)') 提取当前节点的文本内容，你可以根据实际内容长度调整字段类型，比如用NVARCHAR(1000)

场景2：同个客户的所有value值合并为单个单元格 #

如果需要每个客户仅占一行，多个value值用分隔符（比如逗号、分号）拼接，适合导出到Excel使用，使用以下语句：

SELECT
    c.name,
    STRING_AGG(v.value('.', 'VARCHAR(MAX)'), ', ') AS AllExtractedValues
FROM
    client c
    CROSS APPLY (SELECT convert(xml, convert(varbinary(max), c.orders)) AS OrderXml) x
    CROSS APPLY x.OrderXml.nodes('//value') AS T(v)
GROUP BY
    c.name

注意事项 #

• 如果你查询时提示XML命名空间相关错误，说明你的根标签带有命名空间声明，需要在语句开头加上命名空间声明，示例如下：

  WITH XMLNAMESPACES (DEFAULT '这里填你report标签里xmlns属性后面的地址')
  SELECT -- 后面接上面的查询内容
  

• 如果你使用的SQL Server版本低于2017，不支持STRING_AGG函数，可以替换为FOR XML PATH的方式实现字符串拼接。

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