在SQL Server中对比两个XML文件差异，解决查询结果重复问题

需求 #

• 对比SQL Server中存储的两个XML文件（File1为最新版本，File2为旧版本）
• 需识别新增节点、删除节点，以及节点属性的变化
• 现有查询因忽略节点层级，导致同名属性匹配错误，出现重复结果，需修正

示例XML文件 #

<section>
  <oper-ins-procref index-levels="3" moidref="G2020685" proc-uid="G2195890" qualref="+Q100107[11/2022]" title="Mobile Device Data" titleref="T25409" uid="G2292969" uid-ref="R1396412" version="5.0">
      <qualifier>
        <builddates id="Q100107" suppressed="true" title="Vehicles Built From: 11/2022" />
      </qualifier>
    </oper-ins-procref>
    <oper-ins-procref index-levels="3" loc-qual="Vehicles With: Emergency Assistance" moidref="G2128321" proc-uid="G2163427" qualref="+Q107191+Q106763+Q107192" title="Emergency Call System Data" titleref="T27313" uid="G2162708" uid-ref="R1303108" version="2.0">
      <qualifier title="Vehicles With: Emergency Assistance">
        <territory id="Q107191" suppressed="true" title="USA" />
        <feature-mfc category="POSITIVE" id="Q106763" title="Emergency Assistance">
          <mfc code="hksab" type="T" vl="vltf" />
          <mfc code="hksab" type="T" vl="vlts" />
          <!-- 省略重复mfc节点 -->
        </feature-mfc>
        <territory id="Q107192" suppressed="true" title="United States of America" />
      </qualifier>
    </oper-ins-procref>
  </section>
  <section index-levels="1" moidref="G1594458" title="Environment" titleref="T21858" uid="G2323014">
    <link-target moidref="G1594459" option="off" proc-uid="G2145049" qualref="+Q107200" title="Protecting the Environment" titleref="T21859" uid="G2335850" uid-ref="R1428999">
      <qualifier>
        <territory id="Q107200" suppressed="true" title="Brazil" />
      </qualifier>
    </link-target>
    <!-- 省略其他link-target节点 -->
  </section>

现有问题代码 #

DECLARE @XML1 XML
DECLARE @XML2 XML

SET @XML1 = (SELECT TOP 1 Xml FROM TableA NOLOCK WHERE Id=22)

SET @XML2 = (SELECT TOP 1 Xml FROM TableB NOLOCK WHERE Id=34)

;with XML1 as
(
select T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
         T.N.value('.', 'nvarchar(100)') as Value
  from @XML1.nodes('//@*') as T(N)
  ),
XML2 as
(
select T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
         T.N.value('.', 'nvarchar(100)') as Value
  from @XML2.nodes('//@*') as T(N)
  )

select coalesce(XML1.NodeName, XML2.NodeName) as NodeName, 
       XML1.Value as Value1, 
       XML2.Value as Value2
from XML1
  full outer join XML2
    on XML1.NodeName = XML2.NodeName
where coalesce(XML1.Value, '') <> coalesce(XML2.Value, '')

问题说明：仅按属性名关联，忽略了属性所属的节点层级路径，导致不同节点下的同名属性（如多个id、title）被错误匹配，出现重复结果，无法准确区分差异。

修正后的解决方案 #

方案1：准确对比属性差异（含节点路径） #

通过生成完整节点路径+属性名作为唯一标识，确保不同节点下的同名属性能被正确匹配：

DECLARE @XML1 XML
DECLARE @XML2 XML

SET @XML1 = (SELECT TOP 1 Xml FROM TableA NOLOCK WHERE Id=22)
SET @XML2 = (SELECT TOP 1 Xml FROM TableB NOLOCK WHERE Id=34)

;WITH XML1 AS (
    SELECT 
        -- 生成属性的完整层级路径（如section/oper-ins-procref/qualifier/builddates/id）
        T.N.value('string-join(ancestor-or-self::*/local-name(.), ''/'')', 'nvarchar(500)') AS FullPropertyPath,
        T.N.value('local-name(.)', 'nvarchar(100)') AS NodeName,
        T.N.value('.', 'nvarchar(100)') AS Value
    FROM @XML1.nodes('//@*') AS T(N)
), XML2 AS (
    SELECT 
        T.N.value('string-join(ancestor-or-self::*/local-name(.), ''/'')', 'nvarchar(500)') AS FullPropertyPath,
        T.N.value('local-name(.)', 'nvarchar(100)') AS NodeName,
        T.N.value('.', 'nvarchar(100)') AS Value
    FROM @XML2.nodes('//@*') AS T(N)
)
SELECT 
    COALESCE(XML1.FullPropertyPath, XML2.FullPropertyPath) AS FullPropertyPath,
    COALESCE(XML1.NodeName, XML2.NodeName) AS NodeName,
    XML1.Value AS Value1,
    XML2.Value AS Value2,
    -- 明确标记差异类型
    CASE
        WHEN XML1.FullPropertyPath IS NULL THEN '新增属性'
        WHEN XML2.FullPropertyPath IS NULL THEN '删除属性'
        ELSE '属性值修改'
    END AS DiffType
FROM XML1
FULL OUTER JOIN XML2 
    ON XML1.FullPropertyPath = XML2.FullPropertyPath
WHERE 
    COALESCE(XML1.Value, '') <> COALESCE(XML2.Value, '')
    OR XML1.FullPropertyPath IS NULL
    OR XML2.FullPropertyPath IS NULL

方案2：扩展对比元素节点的新增/删除 #

如果需要识别元素节点本身的新增或删除，可添加以下查询：

;WITH XML1_Elements AS (
    SELECT 
        T.N.value('string-join(ancestor-or-self::*/local-name(.), ''/'')', 'nvarchar(500)') AS ElementPath,
        T.N.value('local-name(.)', 'nvarchar(100)') AS ElementName
    FROM @XML1.nodes('//*') AS T(N)
), XML2_Elements AS (
    SELECT 
        T.N.value('string-join(ancestor-or-self::*/local-name(.), ''/'')', 'nvarchar(500)') AS ElementPath,
        T.N.value('local-name(.)', 'nvarchar(100)') AS ElementName
    FROM @XML2.nodes('//*') AS T(N)
)
SELECT 
    COALESCE(XML1_Elements.ElementPath, XML2_Elements.ElementPath) AS ElementPath,
    COALESCE(XML1_Elements.ElementName, XML2_Elements.ElementName) AS ElementName,
    CASE
        WHEN XML1_Elements.ElementPath IS NULL THEN '新增元素节点'
        WHEN XML2_Elements.ElementPath IS NULL THEN '删除元素节点'
    END AS DiffType
FROM XML1_Elements
FULL OUTER JOIN XML2_Elements 
    ON XML1_Elements.ElementPath = XML2_Elements.ElementPath
WHERE 
    XML1_Elements.ElementPath IS NULL
    OR XML2_Elements.ElementPath IS NULL

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