无法从 SQL Server 分析 XML 肥皂 [已关闭]

请尝试以下解决方案。

值得注意的几点：

• 您需要声明和使用 XML 命名空间。
• CData部分需要特殊处理。首先，将其强制转换为 XML 数据类型。后 一切照旧。

SQL算法

DECLARE @xml 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(DEFAULT 'http://www.w3.org/2003/05/soap-envelope'
    , 'http://www.axelot.ru/ESB/package' AS pac
    , 'http://esb.axelot.ru' AS esb)
SELECT x.value('(/*:classData/*:BatchID/text())[1]', 'VARCHAR(50)') AS BatchID
    , x.value('(/*:classData/*:ItemNo/text())[1]', 'VARCHAR(50)') AS ItemNo
    , c.value('(esb:ClassId/text())[1]', 'VARCHAR(20)') AS ClassId
    , c.value('(esb:Id/text())[1]', 'UNIQUEIDENTIFIER') AS Id
FROM @xml.nodes('/Envelope/Body/pac:PushMessage/pac:message') AS t(c)
CROSS APPLY (SELECT TRY_CAST(c.value('(esb:Body/text())[1]', 'NVARCHAR(MAX)') AS XML)) AS t1(x);

输出