SQL Server 2014视图中解析结构多变XML的技术咨询

在SQL Server 2014中处理结构不固定的XML，确实不能用硬编码的XQuery路径来提取数据。我们可以通过递归CTE遍历XML的整个节点层级，自动识别所有元素和属性，然后将它们转换为易读的键值格式。

步骤1：递归遍历XML结构 #

首先，用递归CTE提取所有节点的名称、路径和对应的值。这里假设你的XML存储在某个表的XmlData列中（比如表名为XmlTable）：

WITH XmlNodes AS (
    -- 初始节点：根节点下的所有子元素
    SELECT
        CAST('/' + x.value('local-name(.)', 'nvarchar(100)') AS nvarchar(500)) AS NodePath,
        x.value('local-name(.)', 'nvarchar(100)') AS NodeName,
        x.value('text()[1]', 'nvarchar(max)') AS NodeValue,
        x.query('./*') AS ChildNodes
    FROM XmlTable
    CROSS APPLY XmlData.nodes('/*') AS T(x)
    
    UNION ALL
    
    -- 递归遍历子节点
    SELECT
        CAST(xn.NodePath + '/' + x.value('local-name(.)', 'nvarchar(100)') AS nvarchar(500)),
        x.value('local-name(.)', 'nvarchar(100)'),
        x.value('text()[1]', 'nvarchar(max)'),
        x.query('./*')
    FROM XmlNodes xn
    CROSS APPLY xn.ChildNodes.nodes('*') AS T(x)
)
SELECT NodePath, NodeName, NodeValue
FROM XmlNodes
WHERE NodeValue IS NOT NULL; -- 过滤掉没有文本值的节点

这个CTE会输出所有带有值的节点的完整路径、节点名称和对应的值，不管XML的嵌套结构怎么变，都能自动识别。

步骤2：转换为易读的键值字符串 #

如果要把这些结果格式化成类似附加说明：……、业务流程ID：30001的字符串，由于SQL Server 2014没有STRING_AGG函数，我们可以用FOR XML PATH来拼接：

WITH XmlNodes AS (
    -- 上面的递归CTE代码不变
    SELECT
        CAST('/' + x.value('local-name(.)', 'nvarchar(100)') AS nvarchar(500)) AS NodePath,
        x.value('local-name(.)', 'nvarchar(100)') AS NodeName,
        x.value('text()[1]', 'nvarchar(max)') AS NodeValue,
        x.query('./*') AS ChildNodes
    FROM XmlTable
    CROSS APPLY XmlData.nodes('/*') AS T(x)
    
    UNION ALL
    
    SELECT
        CAST(xn.NodePath + '/' + x.value('local-name(.)', 'nvarchar(100)') AS nvarchar(500)),
        x.value('local-name(.)', 'nvarchar(100)'),
        x.value('text()[1]', 'nvarchar(max)'),
        x.query('./*')
    FROM XmlNodes xn
    CROSS APPLY xn.ChildNodes.nodes('*') AS T(x)
)
SELECT STUFF(
    (SELECT '、' + NodeName + '：' + ISNULL(NodeValue, '')
     FROM XmlNodes
     WHERE NodeValue IS NOT NULL
     FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'),
    1, 1, ''
) AS 易读格式文本;

处理嵌套节点（如Questions） #

如果像你提到的Questions这类嵌套节点，上面的递归CTE会自动遍历到子节点，比如/Root/Questions/Question1这样的路径，你可以根据需求调整输出格式，比如在嵌套节点前加上父节点名称，或者保持完整路径作为键名。

注意事项 #

• 如果XML包含属性，你需要在CTE中额外处理属性节点（用@*代替*来提取属性）。
• 对于大型XML，递归CTE可能会有性能问题，建议根据实际场景添加过滤条件。
• 视图中可以直接包含这个逻辑，但要确保XML列的类型是XML而非NVARCHAR，否则需要先转换：CAST(XmlStringColumn AS XML)。

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