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);
CodePudding user response:
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.