Reading XML HTTP Response in SQL Server

August 9, 2022

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 the values in cmn:GeneralResponse node into a table using XQuery

DECLARE @xml XML = 
N'<soapenv:Envelope
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soapenv:Header
        xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
        xmlns:v1="http://xmlns.fl.com/GetSubscriberInfoRequest/V1"
        xmlns:cor="http://soa.fl.co/coredata_1"
        xmlns:v3="http://xmlns.fl.com/RequestHeader/V3"
        xmlns:v2="http://xmlns.fl.com/ParameterType/V2">
        <cor:SOATransactionID>424b89ab-5d1c-4f51-9aee-63b9f7fdca13</cor:SOATransactionID>
    </soapenv:Header>
    <soapenv:Body
        xmlns:v1="http://xmlns.fl.com/GetSubscriberInfoRequest/V1"
        xmlns:cor="http://soa.mic.co.af/coredata_1"
        xmlns:v3="http://xmlns.fl.com/RequestHeader/V3"
        xmlns:v2="http://xmlns.fl.com/ParameterType/V2">
        <soapenv:Fault>
            <faultcode
                xmlns:env="http://schemas.xmlsoap.org/soap/envelope">env:Server
            </faultcode>
            <faultstring>Service Authentication Failed.</faultstring>
            <detail>
                <ns1:GetSubscriberInfoFault
                    xmlns:ns1="http://xmlns.fl.com/GetSubscriberInfoFault/V1"
                    xmlns:cmn="http://xmlns.fl.com/ResponseHeader/V3">
                    <cmn:ResponseHeader>
                        <cmn:GeneralResponse>
                            <cmn:correlationID>28589788267412344000</cmn:correlationID>
                            <cmn:status>ERROR</cmn:status>
                            <cmn:code>getsubscriberinfo-1055-2505-F</cmn:code>
                            <cmn:description>Service Authentication Failed.</cmn:description>
                        </cmn:GeneralResponse>
                    </cmn:ResponseHeader>
                </ns1:GetSubscriberInfoFault>
            </detail>
        </soapenv:Fault>
    </soapenv:Body>
</soapenv:Envelope>';

This is what I have tried so far but its not working

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.xmlsoap.org/soap/envelope/'
,'http://xmlns.fl.com/GetSubscriberInfoFault/V1' AS ns1
, 'http://xmlns.fl.com/ResponseHeader/V3' AS cmn)
SELECT r.value('(cmn:status/text())[1]','varchar(100)') AS [status]
,r.value('(cmn:description/text())[1]','varchar(100)') AS [description]
FROM @XML.nodes('/Envelope/Body/Fault/ns1:GetSubscriberInfoFault/cmn:ResponseHeader/cmn:GeneralResponse') AS t1(r);

>Solution :

the following works fine (Fiddle)

;WITH XMLNAMESPACES(
     'http://schemas.xmlsoap.org/soap/envelope/'     as soapenv
    ,'http://xmlns.fl.com/GetSubscriberInfoFault/V1' AS ns1
    ,'http://xmlns.fl.com/ResponseHeader/V3'         AS cmn
)
SELECT
   r.value('(cmn:status/text())[1]',     'varchar(100)') AS [status]
  ,r.value('(cmn:description/text())[1]','varchar(100)') AS [description]
FROM @XML.nodes('
    /soapenv:Envelope/soapenv:Body/soapenv:Fault
    /detail/ns1:GetSubscriberInfoFault/cmn:ResponseHeader/cmn:GeneralResponse
  ') AS t1(r);

You were omitting detail in your path. This isn’t in the soapenv namespace so I reverted that default.