Read HTTP XML Response from SQL Server-CodePudding

I need help with reading some nodes in the below XML, it's an HTTP XML response which I store in a variable and need to read the values. I only need to read the values '0' and 'U202207240001'

<RetCode
    xmlns="http://recharge/server">0
</RetCode>
<TaskID
    xmlns="http://recharge/server">U202207240001
</TaskID>

This is the entire XML structure

<?xml version='1.0' encoding='utf-8' ?>
<SOAP-ENV:Envelope
    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
    xmlns:xsd="'http://www.w3.org/2001/XMLSchema'http://www.w3.org/2001/XMLSchema'"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <SOAP-ENV:Body>
        <ns1:GetTaskIDResp
            xmlns:ns1="http://recharge/server">
            <impl:GetTaskIDReply
                xmlns:impl="http://recharge/server">
                <RetCode
                    xmlns="http://recharge/server">0
                </RetCode>
                <TaskID
                    xmlns="http://recharge/server">U202207240001
                </TaskID>
            </impl:GetTaskIDReply>
        </ns1:GetTaskIDResp>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

CodePudding user response：

Please try the following solution.

I had to fix the xmlns:xsd namespace value.

SQL

DECLARE @xml XML = 
'<SOAP-ENV:Envelope
    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <SOAP-ENV:Body>
        <ns1:GetTaskIDResp xmlns:ns1="http://recharge/server">
            <impl:GetTaskIDReply xmlns:impl="http://recharge/server">
                <RetCode xmlns="http://recharge/server">0</RetCode>
                <TaskID xmlns="http://recharge/server">U202207240001</TaskID>
            </impl:GetTaskIDReply>
        </ns1:GetTaskIDResp>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>';

WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS [SOAP-ENV], 
                   'http://recharge/server' AS ns2)
SELECT c.value('(ns2:RetCode/text())[1]','varchar(50)') AS RetCode 
    , c.value('(ns2:TaskID/text())[1]','varchar(50)') AS U202207240001 
FROM @XML.nodes('/SOAP-ENV:Envelope/SOAP-ENV:Body/ns2:GetTaskIDResp/ns2:GetTaskIDReply') AS t(c);

Output

 --------- --------------- 
| RetCode | TaskID        |
 --------- --------------- 
|       0 | U202207240001 |
 --------- ---------------