SQL Server XML列查询：节点提取与注释去重问题

需求与问题 #

要从SQL Server存储的XML列里提取三类内容：

• ReasonCode/Code的节点值
• ReasonInformation的节点值
• XML里的注释内容

目前碰到两个棘手问题：

1. 部分XML里第一个ReasonInformation是无效的，必须跳过它，只提取后面的ReasonCode和对应的ReasonInformation
2. 注释被错误关联到所有节点对，得让注释只对应它所属的那组ReasonCode和ReasonInformation

示例XML #

<Root>
  <!-- 无效节点的注释 -->
  <ReasonInformation>无效信息</ReasonInformation>
  <!-- 对应Code001的注释 -->
  <ReasonCode>
    <Code>001</Code>
  </ReasonCode>
  <ReasonInformation>有效信息1</ReasonInformation>
  <!-- 对应Code002的注释 -->
  <ReasonCode>
    <Code>002</Code>
  </ReasonCode>
  <ReasonInformation>有效信息2</ReasonInformation>
</Root>

建表语句 #

CREATE TABLE XmlData (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    XmlContent XML NOT NULL
);

当前有问题的查询代码 #

SELECT
    xc.value('(ReasonCode/Code)[1]', 'VARCHAR(10)') AS ReasonCode,
    xc.value('(ReasonInformation)[1]', 'VARCHAR(100)') AS ReasonInformation,
    xc.value('(comment())[1]', 'VARCHAR(100)') AS Comment
FROM XmlData
CROSS APPLY XmlContent.nodes('/Root/*') AS XT(xc);

解决方法 #

针对问题1：跳过第一个ReasonInformation #

用ROW_NUMBER()给节点标记位置，直接过滤掉位置为1的ReasonInformation节点。

针对问题2：关联注释到对应节点 #

XML注释是同级节点，用preceding-sibling::comment()[1]获取当前节点的前一个注释，就能确保注释只绑定到它所属的节点组。

最终可用的查询代码 #

WITH ValidNodes AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY XT.xc) AS NodeIndex,
        XT.xc.query('.') AS NodeContent,
        Id
    FROM XmlData
    CROSS APPLY XmlContent.nodes('/Root/*') AS XT(xc)
    -- 过滤掉第一个ReasonInformation节点
    WHERE NOT (XT.xc.value('local-name(.)', 'VARCHAR(50)') = 'ReasonInformation' 
               AND ROW_NUMBER() OVER (PARTITION BY Id ORDER BY XT.xc) = 1)
),
PairedNodes AS (
    SELECT
        Id,
        -- 分组提取ReasonCode和对应的ReasonInformation
        MAX(CASE WHEN NodeContent.value('local-name(/*)', 'VARCHAR(50)') = 'ReasonCode' 
                 THEN NodeContent.value('(/*/Code)[1]', 'VARCHAR(10)') END) AS ReasonCode,
        MAX(CASE WHEN NodeContent.value('local-name(/*)', 'VARCHAR(50)') = 'ReasonInformation' 
                 THEN NodeContent.value('(/*)[1]', 'VARCHAR(100)') END) AS ReasonInformation,
        -- 获取该组对应的前置注释
        MAX(NodeContent.value('(/*/preceding-sibling::comment()[1])[1]', 'VARCHAR(100)')) AS Comment
    FROM ValidNodes
    GROUP BY Id, (NodeIndex + 1) / 2 -- 每两个节点为一组（ReasonCode + ReasonInformation）
)
SELECT ReasonCode, ReasonInformation, Comment
FROM PairedNodes
WHERE ReasonCode IS NOT NULL; -- 只保留有有效ReasonCode的行

代码说明 #

1. ValidNodes CTE：给每个节点分配位置索引，同时剔除第一个无效的ReasonInformation。
2. PairedNodes CTE：通过(NodeIndex + 1)/2把相邻的ReasonCode和ReasonInformation配对成组，用preceding-sibling拿到每组对应的注释。
3. 最后过滤掉没有ReasonCode的行，确保结果都是有效的节点对。

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