如何获取Remittance表中RemittInstr列的所有XML路径超集？

针对Remittance表的RemittInstr XML列，要获取所有记录的XML结构超集（包含所有节点和属性的路径），可使用递归CTE遍历所有XML的节点与属性，最终去重得到完整路径集合，SQL语句如下：

WITH XmlNodeHierarchy AS (
    -- 初始化：提取每条XML的根节点路径
    SELECT
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RecordId,
        x.value('local-name(.)', 'varchar(1000)') AS NodeName,
        CAST('/' + x.value('local-name(.)', 'varchar(1000)') AS varchar(1000)) AS FullPath
    FROM Remittance
    CROSS APPLY RemittInstr.nodes('/*') AS t(x)

    UNION ALL

    -- 递归遍历子节点，生成完整路径
    SELECT
        r.RecordId,
        x.value('local-name(.)', 'varchar(1000)') AS NodeName,
        CAST(xnh.FullPath + '/' + x.value('local-name(.)', 'varchar(1000)') AS varchar(1000)) AS FullPath
    FROM XmlNodeHierarchy xnh
    JOIN (
        SELECT 
            ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RecordId,
            RemittInstr
        FROM Remittance
    ) r ON xnh.RecordId = r.RecordId
    CROSS APPLY r.RemittInstr.nodes(REPLACE(xnh.FullPath, '/', '/*/') + '*') AS t(x)
    WHERE NOT EXISTS (
        SELECT 1 
        FROM XmlNodeHierarchy xnh2 
        WHERE xnh2.RecordId = r.RecordId 
          AND xnh2.FullPath = CAST(xnh.FullPath + '/' + x.value('local-name(.)', 'varchar(1000)') AS varchar(1000))
    )
),
XmlAttributePaths AS (
    -- 提取所有属性的路径
    SELECT
        CAST(xnh.FullPath + '/@' + x.value('local-name(.)', 'varchar(1000)') AS varchar(1000)) AS FullPath
    FROM XmlNodeHierarchy xnh
    JOIN (
        SELECT 
            ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RecordId,
            RemittInstr
        FROM Remittance
    ) r ON xnh.RecordId = r.RecordId
    CROSS APPLY r.RemittInstr.nodes(REPLACE(xnh.FullPath, '/', '/*/') + '/@*') AS t(x)
)
-- 合并节点路径与属性路径，去重后排序
SELECT DISTINCT FullPath AS Path
FROM XmlNodeHierarchy
UNION
SELECT DISTINCT FullPath AS Path
FROM XmlAttributePaths
ORDER BY Path;

执行逻辑说明 #

1. 根节点提取：给每条记录分配唯一ID，提取XML根节点的路径（如/ri）。
2. 子节点递归遍历：基于已生成的节点路径，逐层遍历所有子节点，拼接完整路径（如/ri/Msg、/ri/Msg/AccountNo），避免同一条记录内重复添加路径。
3. 属性路径提取：基于已有的节点路径，提取所有属性的路径（前缀加@，如/ri/Msg/@Type）。
4. 合并去重：将节点路径与属性路径合并，通过DISTINCT去重，最后按路径层级排序输出。

测试验证 #

用你提供的两行测试数据执行该语句，会返回预期的结果：

/ri
/ri/Msg
/ri/Msg/@Type
/ri/Msg/AccountNo
/ri/Msg/BICFI
/ri/Msg/Description
/ri/Msg/Description/@code

大表性能优化建议 #

针对150万条记录的大表，可通过以下方式提升性能：

• 抽样执行：先抽取部分记录（如TOP 10000）快速获取大致结构，确认无遗漏后再全量执行。
• 创建XML索引：给RemittInstr列创建主XML索引，大幅提升XML节点的遍历效率。
• 分批次处理：通过分页或分区表分批次提取路径，避免一次性加载全表导致资源耗尽。

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