无法解析SQL Server的XML SOAP，请求用SQL提取指定字段

给定包含CDATA嵌套的SOAP XML，需要提取BatchID、ItemNo、ClassId、Id四个字段的值，可通过以下SQL语句实现：

完整SQL代码 #

DECLARE @XmlData XML = N'
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope"
               xmlns:pac="http://www.axelot.ru/ESB/package"
               xmlns:esb="http://esb.axelot.ru">
    <soap:Header/>
    <soap:Body>
        <pac:PushMessage>
            <pac:message>
                <esb:Body>
                    <![CDATA[<?xml version="1.0"?>
                    <classData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                               xmlns:xsd="http://www.w3.org/2001/XMLSchema">
                        <GUID/>
                        <SynonymID>46</SynonymID>
                        <BatchID>2CXPN6700PB9GZ4F2O0E6Y3A8</BatchID>
                        <ItemNo>8860637</ItemNo>
                        <OrderID/>
                        <OrderLineID>0</OrderLineID>
                        <NetWeight>60.900</NetWeight>
                        <GrossWeight>63.900</GrossWeight>
                        <ProductionDate>2023-11-06T16:27:33</ProductionDate>
                        <AdvanceDate/>
                        <TerminalID>0</TerminalID>
                        <ProcessUnit>2</ProcessUnit>
                        <SystemType>3</SystemType>
                        <Destination>7</Destination>
                        <CarcassStateCategory>560ec6f3-d352-11ed-bbdd-0050568ed6c5</CarcassStateCategory>
                        <NetWeightDCP06>61.600</NetWeightDCP06>
                    </classData>]]>
                </esb:Body>
                <esb:ClassId>DCP18</esb:ClassId>
                <esb:CreationTime>2023-11-07T10:36:25</esb:CreationTime>
                <esb:Id>3e52661f-c858-4db5-8100-cc0cda7e2a9f</esb:Id>
                <esb:NeedAcknowledgment>false</esb:NeedAcknowledgment>
                <esb:Properties/>
                <esb:Receivers/>
                <esb:ReplyTo/>
                <esb:Source/>
                <esb:Type>DTP</esb:Type>
                <esb:CorrelationId xsi:nil="true"
                                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
            </pac:message>
        </pac:PushMessage>
    </soap:Body>
</soap:Envelope>';

WITH XMLNAMESPACES (
    'http://www.w3.org/2003/05/soap-envelope' AS soap,
    'http://www.axelot.ru/ESB/package' AS pac,
    'http://esb.axelot.ru' AS esb
)
SELECT
    -- 提取CDATA中的BatchID
    CAST(
        @XmlData.value('(/soap:Envelope/soap:Body/pac:PushMessage/pac:message/esb:Body/text())[1]', 'NVARCHAR(MAX)') 
        AS XML
    ).value('(/classData/BatchID/text())[1]', 'NVARCHAR(MAX)') AS BatchID,
    -- 提取CDATA中的ItemNo
    CAST(
        @XmlData.value('(/soap:Envelope/soap:Body/pac:PushMessage/pac:message/esb:Body/text())[1]', 'NVARCHAR(MAX)') 
        AS XML
    ).value('(/classData/ItemNo/text())[1]', 'NVARCHAR(MAX)') AS ItemNo,
    -- 提取外部的ClassId
    @XmlData.value('(/soap:Envelope/soap:Body/pac:PushMessage/pac:message/esb:ClassId/text())[1]', 'NVARCHAR(MAX)') AS ClassId,
    -- 提取外部的Id
    @XmlData.value('(/soap:Envelope/soap:Body/pac:PushMessage/pac:message/esb:Id/text())[1]', 'NVARCHAR(MAX)') AS Id

代码说明 #

1. 命名空间声明：通过XMLNAMESPACES指定SOAP XML中用到的三个命名空间（soap、pac、esb），避免路径解析错误。
2. CDATA内容处理：先通过value()方法提取esb:Body节点中的CDATA文本，再将其转换为XML类型，进而提取内部的BatchID和ItemNo。
3. 外部节点提取：直接使用XPath路径提取esb:ClassId和esb:Id节点的文本值。

执行上述代码后，将得到预期的四个字段值：

内容的提问来源于stack exchange，提问作者Сергей Родин