如何在SQL中比较包含重复节点的两个XML文档

当然可以对包含重复节点的XML文档进行对比！核心在于先把XML的层级结构（尤其是重复的节点）转换成关系型数据格式，这样就能用标准的SQL对比逻辑来找出差异。下面结合你的示例，详细说明在SQL（以SQL Server为例）中如何实现这类对比。

一、核心思路 #

重复节点（比如示例中的<f>）的对比需要先明确规则：

• 是按节点出现的顺序对比（即第一个<f>和第一个<f>比，第二个和第二个比）；
• 还是按节点内容匹配对比（不管顺序，只要内容存在/缺失就算差异）。

两种规则的实现逻辑略有不同，但都需要先把XML节点拆解成二维表结构，再用SQL的集合操作（比如EXCEPT、JOIN）找出差异。

二、SQL实现示例（SQL Server） #

1. 定义待对比的XML变量 #

首先把你的原始XML和修改后的XML存入变量：

DECLARE @OriginalXML XML = N'
<datos>
  <clave1>017</clave1>
  <clave2>017</clave2>
  <clave3>017</clave3>
  <Datos2>
    <f>
      <color>1</color>
      <color1>999</color1>
    </f>
    <f>
      <color>0</color>
      <color1>003</color1>
    </f>
  </Datos2>
</datos>';

DECLARE @CompareXML XML = N'
<datos>
  <clave1>017</clave1>
  <clave2>017</clave2>
  <clave3>017</clave3>
  <Datos2>
    <f>
      <color>2</color>
      <color1>566</color1>
    </f>
    <f>
      <color>0</color>
      <color1>003</color1>
    </f>
  </Datos2>
</datos>';

2. 按节点顺序对比 #

如果需要严格按照<f>节点的出现顺序对比，我们可以给每个节点加上位置索引，再对比相同位置的内容：

WITH OriginalData AS (
    SELECT
        -- 生成节点位置索引，确保顺序一致
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NodePosition,
        -- 提取<color>和<color1>的文本值
        f.value('(color/text())[1]', 'VARCHAR(10)') AS Color,
        f.value('(color1/text())[1]', 'VARCHAR(10)') AS Color1
    FROM @OriginalXML.nodes('/datos/Datos2/f') AS t(f)
),
CompareData AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NodePosition,
        f.value('(color/text())[1]', 'VARCHAR(10)') AS Color,
        f.value('(color1/text())[1]', 'VARCHAR(10)') AS Color1
    FROM @CompareXML.nodes('/datos/Datos2/f') AS t(f)
)
-- 找出两边的差异：原始XML独有、对比XML独有的数据
SELECT '原始XML独有' AS 差异类型, NodePosition AS 节点位置, Color, Color1
FROM OriginalData
EXCEPT
SELECT '原始XML独有' AS 差异类型, NodePosition AS 节点位置, Color, Color1
FROM CompareData
UNION ALL
SELECT '对比XML独有' AS 差异类型, NodePosition AS 节点位置, Color, Color1
FROM CompareData
EXCEPT
SELECT '对比XML独有' AS 差异类型, NodePosition AS 节点位置, Color, Color1
FROM OriginalData;

这段代码会输出：

清晰展示了第一个<f>节点的内容差异。

3. 按节点内容匹配对比 #

如果不需要关心节点顺序，只需要找出两边内容存在/缺失的差异，可以去掉位置索引：

WITH OriginalData AS (
    SELECT
        f.value('(color/text())[1]', 'VARCHAR(10)') AS Color,
        f.value('(color1/text())[1]', 'VARCHAR(10)') AS Color1
    FROM @OriginalXML.nodes('/datos/Datos2/f') AS t(f)
),
CompareData AS (
    SELECT
        f.value('(color/text())[1]', 'VARCHAR(10)') AS Color,
        f.value('(color1/text())[1]', 'VARCHAR(10)') AS Color1
    FROM @CompareXML.nodes('/datos/Datos2/f') AS t(f)
)
SELECT '原始XML独有' AS 差异类型, Color, Color1
FROM OriginalData
EXCEPT
SELECT '原始XML独有' AS 差异类型, Color, Color1
FROM CompareData
UNION ALL
SELECT '对比XML独有' AS 差异类型, Color, Color1
FROM CompareData
EXCEPT
SELECT '对比XML独有' AS 差异类型, Color, Color1
FROM OriginalData;

这段代码的输出和上面类似，但不会显示节点位置，只关注内容差异。

三、其他SQL方言的适配思路 #

如果使用Oracle或PostgreSQL，核心逻辑依然是XML转关系表，只是函数不同：

• Oracle：使用XMLTABLE函数来拆解XML节点；
• PostgreSQL：使用xpath函数结合unnest来提取节点内容。

比如Oracle的简单示例：

SELECT * FROM XMLTABLE(
    '/datos/Datos2/f'
    PASSING @OriginalXML
    COLUMNS
        Color VARCHAR2(10) PATH 'color',
        Color1 VARCHAR2(10) PATH 'color1'
);

总结 #

只要把重复的XML节点转换成关系型数据，就能用熟悉的SQL对比方法找出差异。关键是先确定你的对比规则（顺序优先还是内容优先），再选择对应的实现方式。

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