SQL Server 2019生成带CDATA的嵌套XML及节点过滤问题

需求回顾 #

• 过滤规则：当addressType='C'的地址与同主体下addressType='S'的地址完全相同时，省略该C类型地址节点
• 条件节点：仅当pickupDate变量非空时，生成shipment.consignment.collectiontrg节点
• 约束：保留字段特殊字符（用CDATA包裹），禁止使用动态SQL

解决方案一：基于FOR XML EXPLICIT实现 #

核心思路 #

1. 预处理地址数据：用CTE过滤掉符合重复条件的C类型地址
2. 构建层级行集：在EXPLICIT的行集中，仅当pickupDate非空时生成collectiontrg对应的行
3. CDATA处理：转义字段中的]]>避免破坏CDATA结构，通过!CDATA后缀生成CDATA节点

示例代码 #

-- 假设基础表结构：Orders(orderId, pickupDate)、Addresses(orderId, addressType, street, city, zip, country)
WITH FilteredAddresses AS (
    SELECT 
        a.orderId,
        a.addressType,
        a.street,
        a.city,
        a.zip,
        a.country
    FROM Addresses a
    WHERE 
        -- 保留所有S类型地址
        a.addressType = 'S'
        OR 
        -- 仅保留无匹配S地址的C类型地址
        (
            a.addressType = 'C'
            AND NOT EXISTS (
                SELECT 1 
                FROM Addresses s
                WHERE s.orderId = a.orderId
                  AND s.addressType = 'S'
                  AND s.street = a.street
                  AND s.city = a.city
                  AND s.zip = a.zip
                  AND s.country = a.country
            )
        )
),
XMLRowSet AS (
    -- 1级节点：shipment
    SELECT 
        1 AS Tag, NULL AS Parent,
        NULL AS [shipment!1!],
        NULL AS [consignment!2!],
        NULL AS [collectiontrg!3!pickupDate],
        NULL AS [address!4!type],
        NULL AS [address!4!street!CDATA],
        NULL AS [address!4!city!CDATA],
        NULL AS [address!4!zip!CDATA],
        NULL AS [address!4!country!CDATA]
    FROM Orders
    UNION ALL
    -- 2级节点：consignment
    SELECT 
        2 AS Tag, 1 AS Parent,
        NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
    FROM Orders
    UNION ALL
    -- 3级节点：collectiontrg（仅pickupDate非空时生成）
    SELECT 
        3 AS Tag, 2 AS Parent,
        NULL, NULL, o.pickupDate, NULL, NULL, NULL, NULL, NULL
    FROM Orders o
    WHERE o.pickupDate IS NOT NULL
    UNION ALL
    -- 4级节点：address（来自过滤后的地址集）
    SELECT 
        4 AS Tag, 2 AS Parent,
        NULL, NULL, NULL, fa.addressType,
        REPLACE(fa.street, ']]>', ']]]]><![CDATA[') AS [address!4!street!CDATA],
        REPLACE(fa.city, ']]>', ']]]]><![CDATA[') AS [address!4!city!CDATA],
        REPLACE(fa.zip, ']]>', ']]]]><![CDATA[') AS [address!4!zip!CDATA],
        REPLACE(fa.country, ']]>', ']]]]><![CDATA[') AS [address!4!country!CDATA]
    FROM FilteredAddresses fa
    JOIN Orders o ON fa.orderId = o.orderId
)
-- 生成最终XML
SELECT * FROM XMLRowSet
ORDER BY [shipment!1!], [consignment!2!], [address!4!type]
FOR XML EXPLICIT, TYPE;

解决方案二：基于FOR XML PATH实现（更简洁） #

核心思路 #

1. 同样用CTE过滤重复的C地址
2. 用CASE语句控制collectiontrg节点的生成（为空时不输出）
3. 通过CAST('<![CDATA[...]]>' AS XML)实现CDATA包裹，同时转义特殊字符

示例代码 #

WITH FilteredAddresses AS (
    SELECT 
        a.orderId,
        a.addressType,
        a.street,
        a.city,
        a.zip,
        a.country
    FROM Addresses a
    WHERE 
        a.addressType = 'S'
        OR 
        (
            a.addressType = 'C'
            AND NOT EXISTS (
                SELECT 1 
                FROM Addresses s
                WHERE s.orderId = a.orderId
                  AND s.addressType = 'S'
                  AND s.street = a.street
                  AND s.city = a.city
                  AND s.zip = a.zip
                  AND s.country = a.country
            )
        )
)
SELECT 
    (
        SELECT 
            -- 条件生成collectiontrg节点
            CASE WHEN o.pickupDate IS NOT NULL THEN
                (SELECT o.pickupDate AS pickupDate FOR XML PATH('collectiontrg'), TYPE)
            END,
            -- 生成过滤后的地址节点
            (
                SELECT 
                    fa.addressType AS [@type],
                    CAST('<![CDATA[' + REPLACE(fa.street, ']]>', ']]]]><![CDATA[') + ']]>' AS XML) AS street,
                    CAST('<![CDATA[' + REPLACE(fa.city, ']]>', ']]]]><![CDATA[') + ']]>' AS XML) AS city,
                    CAST('<![CDATA[' + REPLACE(fa.zip, ']]>', ']]]]><![CDATA[') + ']]>' AS XML) AS zip,
                    CAST('<![CDATA[' + REPLACE(fa.country, ']]>', ']]]]><![CDATA[') + ']]>' AS XML) AS country
                FROM FilteredAddresses fa
                WHERE fa.orderId = o.orderId
                FOR XML PATH('address'), TYPE
            )
        FOR XML PATH('consignment'), TYPE
    )
FROM Orders o
FOR XML PATH('shipment'), TYPE;

关键说明 #

• 两种方案均无需动态SQL，完全通过静态查询逻辑实现需求
• CDATA处理中，REPLACE(filed, ']]>', ']]]]><![CDATA[')是为了避免字段内的]]>截断CDATA块，这是SQL Server中生成合法CDATA的标准写法
• 若XML层级复杂，EXPLICIT更易精准控制节点结构；若层级简单，PATH写法更简洁易维护

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