比较并找出 SQL 中两个 XML 文件之间的差异

问：

我必须找到两个XML文件之间的区别 - 最新文件（File1）和以前的版本（File2）。更新的 XML 文件可能具有其他节点，或者某些节点可能已被删除。我必须比较这两个 XML，并且需要显示 SQL Server 中的差异。

示例 XML 文件

  <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 code="hksab" type="C" vl="vlge" />
          <mfc code="hksab" type="C" vl="vlng" />
          <mfc code="hksab" type="C" vl="vl4a" />
          <mfc code="hksab" type="C" vl="vlgh" />
          <mfc code="hksab" type="C" vl="vle1" />
          <mfc code="hksab" type="T" vl="vlfd" />
          <mfc code="hksab" type="C" vl="vldr" />
          <mfc code="hksab" type="C" vl="vlhd" />
          <mfc code="hksab" type="C" vl="vlhe" />
          <mfc code="hksab" type="C" vl="vlf7" />
          <mfc code="hksab" type="C" vl="vlhc" />
          <mfc code="hksab" type="C" vl="vlgw" />
          <mfc code="hksab" type="T" vl="vlth" />
          <mfc code="hksab" type="T" vl="vlg1" />
          <mfc code="hksab" type="T" vl="vlb8" />
          <mfc code="hksab" type="T" vl="vlek" />
          <mfc code="hksab" type="T" vl="vlg4" />
          <mfc code="hksab" type="T" vl="vlra" />
          <mfc code="hksab" type="T" vl="vlrb" />
          <mfc code="hksab" type="C" vl="vltw" />
          <mfc code="hksab" type="C" vl="vltd" />
          <mfc code="hksab" type="C" vl="vltc" />
          <mfc code="hksab" type="T" vl="vlrc" />
          <mfc code="hksab" type="C" vl="vlte" />
          <mfc code="hksab" type="C" vl="vlzj" />
          <mfc code="hksab" type="T" vl="vlrd" />
          <mfc code="hksab" type="T" vl="vltu" />
          <mfc code="hksab" type="T" vl="vlfh" />
        </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 moidref="G1594459" proc-uid="G2188330" qualref="+Q107198" title="Protecting the Environment" titleref="T21859" uid="G2335851" uid-ref="R1429000">
      <qualifier>
        <territory id="Q107198" suppressed="true" title="Taiwan" />
      </qualifier>
    </link-target>
    <link-target moidref="G1594459" proc-uid="G2322795" title="Protecting the Environment" titleref="T21859" uid="G2323015" uid-ref="R1419074" />
    <oper-ins-procref index-levels="3" moidref="G1594459" proc-uid="G1727320" qualref="+Q107192" title="Protecting the Environment" titleref="T21859" uid="G2323016" uid-ref="R1419075" version="9.0">
      <qualifier>
        <territory id="Q107192" suppressed="true" title="United States of America" />
      </qualifier>
    </oper-ins-procref>
  </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)

--SELECT @XML1, @XML2

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

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

;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, '')
```