Can't parse XML soap from SQL Server

Please help with parsing values with SQL from XML:

<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>

I need to parse this values from XML:

BatchID :2CXPN6700PB9GZ4F2O0E6Y3A8
ItemNo: 8860637
ClassId: DCP18<
Id: 3e52661f-c858-4db5-8100-cc0cda7e2a9f

Solution

Please try the following solution.

Notable points:

You need to declare and use XML namespaces.
CData section needs special treatment. First, cast it as XML data type. After that business as usual.

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);

Output

BatchID	ItemNo	ClassId	Id
2CXPN6700PB9GZ4F2O0E6Y3A8	8860637	DCP18	3E52661F-C858-4DB5-8100-CC0CDA7E2A9F