SQL Server 2019 FOR XML 嵌套节点保留 CDATA

问：

我必须构建此有效载荷

<?xml version="1.0" encoding="utf-8"?>
<shipment>
    <software>
        <application>MYRTL</application>
        <version>1.0</version>
    </software>
    <security>
        <customer>X00000</customer>
        <user>X00000</user>
        <password>password1</password>
        <langid>IT</langid>
    </security>
    <consignment action="I" cashondeliver="N" international="N" insurance="N">
        <labelType>T</labelType>
        <senderAccId>200200</senderAccId>
        <consignmenttype>T</consignmenttype>
        <actualweight>00008000</actualweight>
        <actualvolume>0000018</actualvolume>
        <totalpackages>2</totalpackages>
        <packagetype>C</packagetype>
        <division>D</division>
        <product>N</product>
        <insurancevalue>0000000000000</insurancevalue>
        <insurancecurrency>EUR</insurancecurrency>
        <reference><![CDATA[22X000223]]></reference>
        <collectiondate>20220818</collectiondate>
        <termsofpayment>S</termsofpayment>
        <systemcode>RL</systemcode>
        <systemversion>1.0</systemversion>
        <codfvalue>0000000000000</codfvalue>
        <codfcurrency>EUR</codfcurrency>
        <goodsdesc><![CDATA[Bread, Butter & Puré]]></goodsdesc>
        <addresses>
            <address>
                <addressType>S</addressType>
                <vatno>123456789123</vatno>
                <addrline1><![CDATA[Via Mondovì, n° 23]]></addrline1>
                <postcode><![CDATA[20125]]></postcode>
                <phone1><![CDATA[345]]></phone1>
                <phone2><![CDATA[3456345]]></phone2>
                <name><![CDATA[Jack & Joe srl]]></name>
                <country><![CDATA[IT]]></country>
                <town><![CDATA[Arquà Polesine]]></town>
                <province><![CDATA[RO]]></province>
                <email><![CDATA[mail@jack_and_joe.it]]></email>
            </address>
            <address>
                <addressType>C</addressType>
                <addrline1><![CDATA[12° Reggimento Granatieri, 14]]></addrline1>
                <postcode><![CDATA[00195]]></postcode>
                <phone1><![CDATA[321]]></phone1>
                <phone2><![CDATA[3214321]]></phone2>
                <name><![CDATA[Giosuè Rossë]]></name>
                <country><![CDATA[IT]]></country>
                <town><![CDATA[Gambolo']]></town>
                <province><![CDATA[TV]]></province>
                <email><![CDATA[[email protected]]]></email>
            </address>
            <address>
                <addressType>R</addressType>
                <addrline1><![CDATA[Hauptstraße 13]]></addrline1>
                <postcode><![CDATA[34100]]></postcode>
                <phone1><![CDATA[333]]></phone1>
                <phone2><![CDATA[333444555]]></phone2>
                <name><![CDATA[Noè Giassù]]></name>
                <country><![CDATA[IT]]></country>
                <town><![CDATA[Völs am Schlern]]></town>
                <province><![CDATA[BZ]]></province>
                <email><![CDATA[[email protected]]]></email>
            </address>
        </addresses>
        <collectiontrg>
            <priopntime>0900</priopntime>
            <priclotime>1200</priclotime>
            <secopntime>1400</secopntime>
            <secclotime>1800</secclotime>
            <availabilitytime>1600</availabilitytime>
            <pickupdate>18.08.2022</pickupdate>
            <pickuptime>1600</pickuptime>
            <pickupdays>1</pickupdays>
            <pickupinstr><![CDATA[Test Shipment ===> DO NOT COLLECT <===]]></pickupinstr>
        </collectiontrg>
        <dimensions itemaction="I">
            <itemsequenceno>1</itemsequenceno>
            <itemtype>C</itemtype>
            <itemreference><![CDATA[22X0002223_1]]></itemreference>
            <volume>0000009</volume>
            <weight>00003000</weight>
            <length>030000</length>
            <heigh>010000</heigh>
            <width>030000</width>
            <quantity>1</quantity>
        </dimensions>
        <dimensions itemaction="I">
            <itemsequenceno>2</itemsequenceno>
            <itemtype>C</itemtype>
            <itemreference><![CDATA[22X0002223_2]]></itemreference>
            <volume>0000009</volume>
            <weight>00005000</weight>
            <length>030000</length>
            <heigh>010000</heigh>
            <width>030000</width>
            <quantity>1</quantity>
        </dimensions>
    </consignment>
</shipment>

我有一个使用 T-SQL 的坏主意，因为所有数据都在 SQL Server DB 中

我认为这很容易，实际上确实如此，因为只需要嵌套一些子查询。FOR XML PATH, TYPE

当考虑到某些字段可能包含非标准字符时，会出现问题，因此最好使用某些 CDATA 字段。

我遇到了几个问题，因为似乎保留 CDATA 的唯一方法是使用它似乎已被弃用。 但是，很难找到文件。FOR XML EXPLICIT

幸运的是，我找到了这篇文章，它帮助我走上了相反的道路：

因此，我构建了一个 XML Explicit 格式：sproc

SELECT  1 AS Tag,
        NULL AS Parent,

        'MYRTL' AS 'software!1!application!element',
        '1.0'   AS 'software!1!version!element',
       
        NULL AS 'security!2!customer!element',
        ...
        NULL AS 'security!2!langid!element',

        NULL AS 'consignment!3!action',
        ...
        NULL AS 'consignment!3!goodsdesc!CDATA',

        NULL AS 'addresses!4!address',

        NULL AS 'address!5!addressType!element',
        ...
        NULL AS 'address!5!town!CDATA',
       
        ...

        NULL  AS 'collectiontrg!9!priopntime!element',
        ...
        NULL  AS 'collectiontrg!9!pickupdate!element',



UNION ALL
SELECT  2 AS Tag,
        NULL AS Parent,
        ...
UNION ALL
SELECT  3 AS Tag,
        NULL AS Parent,
        ...
UNION ALL
SELECT  9 AS Tag,
        3 AS Parent,
        ...

FOR XML EXPLICIT, ROOT('shipment') 

它似乎运行良好......尽管我认为必须有更好的方法来构建它。

现在我还有一个我不知道如何解决的问题，或者更好的是，我可以使用动态查询来解决它，但我会避免它：

新问题是节点shipment.consignment.addresses.addressaddressType=='C'

如果它包含与 where 相同的值，则必须省略它shipment.consignment.addresses.addressaddressType=='S'

此外，仅当变量不为 null 时，节点才必须出现shipment.consignment.collectiontrgpickupDate

有没有办法避免动态查询？

有没有更好的方法来构建此查询？ 谢谢