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 want to read just the below nodes from the entire XML which is line 5,6 and 7 into another variables

<ersReference>2022051811593846801000016</ersReference>
    <resultCode>0</resultCode>
    <resultDescription>You have topped up 6.00</resultDescription>

I have tried several methods like OPENXML but couldn't find my way around it. I am using SQL Server 2014.

Entire XML structure:

<soap:Envelope
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
    <ns2:requestTopupResponse
        xmlns:ns2="http://external.interfaces.ers.seamless.com/">
        <return>
            <ersReference>2022051811593846801000016</ersReference>
            <resultCode>0</resultCode>
            <resultDescription>You have topped up 6.00</resultDescription>
            <requestedTopupAmount>
                <currency>USD</currency>
                <value>6</value>
            </requestedTopupAmount>
            <senderPrincipal>
                <principalId>
                    <id>RETAILER0001</id>
                    <type>RESELLERID</type>
                </principalId>
                <principalName>xxx2343</principalName>
                <accounts>
                    <account>
                        <accountSpecifier>
                            <accountId>RETAILER0001</accountId>
                            <accountTypeId>RESELLER</accountTypeId>
                        </accountSpecifier>
                        <balance>
                            <currency>USD</currency>
                            <value>100000000444.00</value>
                        </balance>
                    </account>
                </accounts>
                <status>Active</status>
                <msisdn>001327874564</msisdn>
                <serviceIds/>
            </senderPrincipal>
            <topupAccountSpecifier>
                <accountId>001667543443</accountId>
                <accountTypeId>AIRTIME</accountTypeId>
            </topupAccountSpecifier>
            <topupAmount>
                <currency>USD</currency>
                <value>6.00</value>
            </topupAmount>
            <topupPrincipal>
                <principalId>
                    <id>00199806443332</id>
                    <type>SUBSCRIBERID</type>
                </principalId>
                <principalName/>
                <accounts>
                    <account>
                        <accountDescription>Account Balance</accountDescription>
                        <accountSpecifier>
                            <accountId>0018845343232</accountId>
                            <accountTypeId>AIRTIME</accountTypeId>
                        </accountSpecifier>
                        <balance>
                            <currency>USD</currency>
                            <value>0.033</value>
                        </balance>
                    </account>
                    <account>
                        <accountSpecifier>
                            <accountId>00188902232333</accountId>
                            <accountTypeId>BUND</accountTypeId>
                        </accountSpecifier>
                    </account>
                </accounts>
            </topupPrincipal>
        </return>
    </ns2:requestTopupResponse>
</soap:Body>
</soap:Envelope>

CodePudding user response：

You can use XQuery to achieve this. You'll need to ensure you define your namespaces first as well though:

WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS soap, 
                   'http://external.interfaces.ers.seamless.com/' AS ns2)
SELECT rTR.r.value('(ersReference/text())[1]','varchar(50)') AS ersReference,
       rTR.r.value('(resultCode/text())[1]','int') AS resultCode,
       rTR.r.value('(resultDescription/text())[1]','varchar(50)') AS resultDescription
FROM @XML.nodes('soap:Envelope/soap:Body/ns2:requestTopupResponse/return') rTR(r);

db<>fiddle