sql - 在 SQL Server 中合并两个 XML 列

Question

希望有人可以帮助我进行 SQL 查询，我正在尝试合并两个 XML 列，我目前正在使用 SQL Azure 2019。

第一个 XML

<HOME>
    <VALIDITYLIST>
        <VALIDITY STATE="1">
            <VALIDITYTYPE>1</VALIDITYTYPE>
            <GROUPCODE>DEFAULT</GROUPCODE>
            <ENTRY/>
            <CARD>2</CAR>
            <GIFTAID/>
            <VARIABLERANGE>false</VARIABLERANGE>
            <DAYS>365</DAYS>
            <NOTOPERATING>false</NOTOPERATING>
            <VALIDITYLIST/>
            <YPERESTRICTIONLIST/>
            <METRALOCKERV2>
                <LOCKERITEMID/>
            </METRALOCKERV2>
            <REQUIREDVAREXPDATE/>
        </VALIDITY>
    </VALIDITYLIST>
</HOME>

第二个 XML

<HOME>
    <VALIDITYLIST>
        <VALIDITY STATE="1">
            <VALIDITYTYPE>1</VALIDITYTYPE>
            <GROUPCODE>DEFAULT</GROUPCODE>
            <GIFTAID/>
            <DYNAMICP/>
            <VALIDITYLIST>
                <VALIDITY STATE="1">
                    <VALIDITYTYPE>2</VALIDITYTYPE>
                    <EVENT>3</EVENT>
                    <ENTRYTYPE>2</ENTRYTYPE>
                    <NUMENTRY>1</NUMENTRY>
                </VALIDITY>
            </VALIDITYLIST>
        </VALIDITY>
    </VALIDITYLIST>
</HOME>

SQL 会以某种方式合并两个 xml，并仅返回存在值的节点。类似的东西。

<HOME>
    <VALIDITYLIST>
        <VALIDITY>
            <VALIDITYTYPE>1</VALIDITYTYPE>
            <GROUPCODE>DEFAULT</GROUPCODE>
            <CARD>2</CAR>
            <VARIABLERANGE>false</VARIABLERANGE>
            <DAYS>365</DAYS>
            <NOTOPERATING>false</NOTOPERATING>
            <VALIDITYLIST>
                <VALIDITY>
                    <VALIDITYTYPE>2</VALIDITYTYPE>
                    <EVENT>3</EVENT>
                    <ENTRYTYPE>2</ENTRYTYPE>
                    <NUMENTRY>1</NUMENTRY>
                </VALIDITY>
            </VALIDITYLIST>
        </VALIDITY>
    </VALIDITYLIST>
</HOME> 

感谢大家分享您的想法[下面编辑了部分]

我仍然想在 SQL 中处理。由于节点是固定的，我正在考虑通过读取单独的 XML 创建两个表，然后根据值构建新的 XML，现在我陷入了第一部分，读取 XML。这就是我想到的，我应该期望在读取第二个 XML 但返回 NULL 时看到 1 和 2 的值，你能看到我在这里做错了什么吗？

select @XML2.value('(/HOME/VALIDITYTYPE/node())[1]', 'nvarchar(max)') as VALIDITYTYPE
, @XML2.value('(/HOME/VALIDITYLIST/VALIDITYLIST/VALIDITYTYPE/node())[1]', 'nvarchar(max)') as VALIDITYTYPE

Answer 1

是的，这在纯 SQL 中是可能的，使用 XQuery 并完全连接两个 XML，然后使用 FOR XML 重建它

SELECT *,
  NewXml = (
    SELECT
      VALIDITYLIST = (
        SELECT
          ISNULL(x1.query('.'), x2.query('.'))
        FROM
          t.Xml1.nodes('HOME/VALIDITYLIST/VALIDITY/*[.//text()]') x1(x1)
        FULL JOIN
          t.Xml2.nodes('HOME/VALIDITYLIST/VALIDITY/*[.//text()]') x2(x2)
          ON x2.x2.value('local-name(.)','nvarchar(30)') = x1.x1.value('local-name(.)','nvarchar(30)')
        FOR XML PATH(''), ROOT('VALIDITY'), TYPE
      )
    FOR XML PATH(''), ROOT('HOME'), TYPE
  )
FROM YourTable t;

谓词*[.//text()] 检查当前节点是否在任意深度具有任何后代文本元素。

我们通过 local-name(.) 连接当前节点的名称，如果可用，则采用第一列的结果，否则采用第二列的结果。如果列未命名，FOR XML 不会添加节点名称。

这一切都假设您始终只有一个 HOME/VALIDITYLIST/VALIDITY 节点，否则会更加复杂。

db<>fiddle