SQL Azure 2019中XML列合并及节点读取问题求助

需求说明 #

使用SQL Azure 2019，需合并两个XML列，仅保留包含有效值的节点，最终得到指定结构的XML：

第一个XML列内容： #

<HOME>
    <VALIDITYLIST>
        <VALIDITY STATE="1">
            <VALIDITYTYPE>1</VALIDITYTYPE>
            <GROUPCODE>DEFAULT</GROUPCODE>
            <ENTRY/>
            <CARD>2</CARD> <!-- 原代码中</CAR>为笔误，已修正为</CARD> -->
            <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>

期望合并后的XML： #

<HOME>
    <VALIDITYLIST>
        <VALIDITY>
            <VALIDITYTYPE>1</VALIDITYTYPE>
            <GROUPCODE>DEFAULT</GROUPCODE>
            <CARD>2</CARD>
            <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> 

查询XML返回NULL的问题分析 #

你执行以下语句读取第二个XML的VALIDITYTYPE值（期望得到1和2），但返回NULL：

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

问题出在XPath路径错误：

1. 第一个XPath /HOME/VALIDITYTYPE/node()：VALIDITYTYPE并非直接嵌套在HOME节点下，正确层级是HOME/VALIDITYLIST/VALIDITY/VALIDITYTYPE，且用text()提取文本值比node()更直接。
2. 第二个XPath /HOME/VALIDITYLIST/VALIDITYLIST/VALIDITYTYPE/node()：缺少中间的VALIDITY节点，正确层级是HOME/VALIDITYLIST/VALIDITY/VALIDITYLIST/VALIDITY/VALIDITYTYPE。

修正后的查询语句：

select 
    @XML2.value('(/HOME/VALIDITYLIST/VALIDITY/VALIDITYTYPE/text())[1]', 'nvarchar(max)') as VALIDITYTYPE1,
    @XML2.value('(/HOME/VALIDITYLIST/VALIDITY/VALIDITYLIST/VALIDITY/VALIDITYTYPE/text())[1]', 'nvarchar(max)') as VALIDITYTYPE2

XML合并实现方案 #

基于需求（仅保留有值节点），可通过提取非空节点、合并层级、重构XML的方式实现：

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

DECLARE @XML2 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>'

-- 提取第一个XML顶层VALIDITY节点的非空子节点
WITH XML1Data AS (
    SELECT
        n.value('local-name(.)', 'nvarchar(100)') AS NodeName,
        n.value('text()[1]', 'nvarchar(max)') AS NodeValue
    FROM @XML1.nodes('/HOME/VALIDITYLIST/VALIDITY/*') AS t(n)
    WHERE n.value('text()[1]', 'nvarchar(max)') IS NOT NULL
),
-- 提取第二个XML顶层VALIDITY节点的非空子节点
XML2Data AS (
    SELECT
        n.value('local-name(.)', 'nvarchar(100)') AS NodeName,
        n.value('text()[1]', 'nvarchar(max)') AS NodeValue
    FROM @XML2.nodes('/HOME/VALIDITYLIST/VALIDITY/*') AS t(n)
    WHERE n.value('text()[1]', 'nvarchar(max)') IS NOT NULL
),
-- 合并顶层节点：优先保留第一个XML的节点，补充第二个XML独有的非空节点
MergedTopNodes AS (
    SELECT NodeName, NodeValue FROM XML1Data
    UNION ALL
    SELECT NodeName, NodeValue FROM XML2Data
    WHERE NodeName NOT IN (SELECT NodeName FROM XML1Data)
),
-- 提取第二个XML中的嵌套VALIDITYLIST结构
NestedValidity AS (
    SELECT @XML2.query('/HOME/VALIDITYLIST/VALIDITY/VALIDITYLIST') AS NestedXML
)
-- 构造最终合并后的XML
SELECT 
    (
        SELECT
            (
                SELECT 
                    CASE WHEN NodeName = 'VALIDITYLIST' THEN NestedXML ELSE NodeValue END AS [*]
                FROM MergedTopNodes
                LEFT JOIN NestedValidity ON NodeName = 'VALIDITYLIST'
                FOR XML PATH(''), TYPE
            ) AS [VALIDITY]
        FOR XML PATH('VALIDITYLIST'), ROOT('HOME'), TYPE
    ) AS MergedXML

该方案会：

• 自动过滤所有无文本值的空节点
• 合并两个XML的顶层非空节点，优先保留第一个XML的内容
• 完整保留第二个XML中的嵌套VALIDITYLIST结构

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