SQL Server中如何将XML解析为(XPath, Value)键值对结果集

完全不需要手动遍历XML节点，使用SQL Server原生XML处理方法即可实现需求，同时支持自定义起始查询路径。

第一步：定义测试数据与基础参数 #

DECLARE @xml XML = N'
<A>
    <B>123</B>
    <C>
          <Cs>234</Cs>
          <Cs>345</Cs>
          <Cs>12</Cs>
          <Cs>2346</Cs>
    </C>
</A>'
-- 可自定义起始路径，修改为'/A/C'即可仅返回C节点下的内容
DECLARE @BasePath NVARCHAR(100) = N'/A'

第二步：核心查询实现（固定起始路径版本） #

;WITH LeafNodes AS (
    SELECT
        -- 拼接符合要求格式的XPath路径
        N'(' + @BasePath + 
        CASE WHEN T.ref.value('local-name(..)[1]', 'NVARCHAR(100)') <> '' 
             THEN N'/' + T.ref.value('local-name(..)[1]', 'NVARCHAR(100)') ELSE N'' END
        + N'/' + T.ref.value('local-name(.)[1]', 'NVARCHAR(100)') + N')[' 
        + CAST(T.ref.value('for $i in . return count(../*[local-name() = local-name($i)] and . << $i) +1', 'INT') AS NVARCHAR(10)) 
        + N']' AS xpath,
        T.ref.value('.', 'NVARCHAR(MAX)') AS value
    -- 匹配所有无后代节点的叶子节点
    FROM @xml.nodes(N'//*[not(*)]') T(ref)
)
SELECT xpath, value FROM LeafNodes

第三步：动态起始路径版本 #

如果需要灵活切换起始路径，使用动态SQL实现：

DECLARE @BasePath NVARCHAR(100) = N'/A/C'
DECLARE @Sql NVARCHAR(MAX) = N'
SELECT
    N''(' + @BasePath + N'/'' + T.ref.value(''local-name(.)[1]'', ''NVARCHAR(100)'') + N'')['' 
    + CAST(T.ref.value(''for $i in . return count(../*[local-name() = local-name($i)] and . << $i) +1'', ''INT'') AS NVARCHAR(10)) 
    + N'']'' AS xpath,
    T.ref.value(''.'', ''NVARCHAR(MAX)'') AS value
FROM @xml.nodes(''' + @BasePath + N'//*[not(*)]'') T(ref)
'
EXEC sp_executesql @Sql, N'@xml XML', @xml = @xml

实现效果 #

• 起始路径设为/A时，输出和你提供的示例结果完全一致
• 起始路径设为/A/C时，仅返回4条Cs节点对应的XPath与值，符合额外要求
• 全流程依赖SQL Server原生XML处理能力，无手动遍历逻辑，大XML场景下性能远优于递归遍历实现

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