How to read XML HTTP Response in 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 want to read all the nodes that have the v31,v11,v12 namespace prefix into a table using XQuery

<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/InfoRequest/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">
      <cor:SOATransactionID>96514584-be43-40f7-9335-b17f6d6669bd</cor:SOATransactionID>
   </soapenv:Header>
   <soapenv:Body xmlns:v1="http://xmlns.fl.com/InfoRequest/V1" xmlns:cor="http://soa.fl.co.af/coredata_1" xmlns:v3="http://xmlns.fl.com/RequestHeader/V3" xmlns:v2="http://xmlns.fl.com/ParameterType/V2">
      <v11:InfoResponse xmlns:v11="http://xmlns.fl.com/InfoResponse/V1">
         <v31:ResponseHeader xmlns:v31="http://xmlns.fl.com/ResponseHeader/V3">
            <v31:GeneralResponse>
               <v31:correlationID>AD-nXfJhT5ZMVEmKkut</v31:correlationID>
               <v31:status>OK</v31:status>
               <v31:code>Successfully-001</v31:code>
               <v31:description>successfully.</v31:description>
            </v31:GeneralResponse>
         </v31:ResponseHeader>
         <v11:responseBody>
            <v11:msisdn> 149012098788</v11:msisdn>
            <v11:customer>
               <v12:name xmlns:v12="http://xmlns.fl.com/CustomerType/V1">FL Company</v12:name>
               <v12:clientCode xmlns:v12="http://xmlns.fl.com/CustomerType/V1">5690001212</v12:clientCode>
               <v12:firstName xmlns:v12="http://xmlns.fl.com/CustomerType/V1">FL Company</v12:firstName>
               <v12:lastName xmlns:v12="http://xmlns.fl.com/CustomerType/V1">RA</v12:lastName>
               <v12:birthDate xmlns:v12="http://xmlns.fl.com/CustomerType/V1">1997-08-07</v12:birthDate>
               <v12:gender xmlns:v12="http://xmlns.fl.com/CustomerType/V1">Female</v12:gender>
               <v12:address xmlns:v12="http://xmlns.fl.com/CustomerType/V1">[email protected]</v12:address>
               <v12:email xmlns:v12="http://xmlns.fl.com/CustomerType/V1">[email protected]</v12:email>
               <v12:nationality xmlns:v12="http://xmlns.fl.com/CustomerType/V1">233</v12:nationality>
               <v12:clientGrade xmlns:v12="http://xmlns.fl.com/CustomerType/V1">CustomerLevel2</v12:clientGrade>
               <v12:clientType xmlns:v12="http://xmlns.fl.com/CustomerType/V1">Company</v12:clientType>
               <v12:clientState xmlns:v12="http://xmlns.fl.com/CustomerType/V1">Unspecified</v12:clientState>
               <v12:admissionDate xmlns:v12="http://xmlns.fl.com/CustomerType/V1">2022-07-16T08:19:20</v12:admissionDate>
               <v12:additionalProperties xmlns:v12="http://xmlns.fl.com/CustomerType/V1">
                  <v2:ParameterType>
                     <v2:parameterName>Value</v2:parameterName>
                     <v2:parameterValue>0.0</v2:parameterValue>
                  </v2:ParameterType>
               </v12:additionalProperties>
            </v11:customer>
            <v11:subscriber>
               <v12:imsi xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">990423434534</v12:imsi>
               <v12:clientCode xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">7870559304234</v12:clientCode>
               <v12:brandId xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">120</v12:brandId>
               <v12:language xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">English</v12:language>
               <v12:smsLanguage xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">English</v12:smsLanguage>
               <v12:ussdLanguage xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">English</v12:ussdLanguage>
               <v12:customerType xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">Foreign</v12:customerType>
               <v12:initialCredit xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">30</v12:initialCredit>
               <v12:mainProductID xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">990877687</v12:mainProductID>
            </v11:subscriber>
         </v11:responseBody>
      </v11:InfoResponse>
   </soapenv:Body>
</soapenv:Envelope>

CodePudding user response：

Please try the following.

The XML sample has lots of namespaces. You should be disciplined with them as well as with XPath expressions.

SQL

DECLARE @xml XML = 
N'<?xml version="1.0"?>
<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/InfoRequest/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">
        <cor:SOATransactionID>96514584-be43-40f7-9335-b17f6d6669bd</cor:SOATransactionID>
    </soapenv:Header>
    <soapenv:Body xmlns:v1="http://xmlns.fl.com/InfoRequest/V1" xmlns:cor="http://soa.fl.co.af/coredata_1" xmlns:v3="http://xmlns.fl.com/RequestHeader/V3" xmlns:v2="http://xmlns.fl.com/ParameterType/V2">
        <v11:InfoResponse xmlns:v11="http://xmlns.fl.com/InfoResponse/V1">
            <v31:ResponseHeader xmlns:v31="http://xmlns.fl.com/ResponseHeader/V3">
                <v31:GeneralResponse>
                    <v31:correlationID>AD-nXfJhT5ZMVEmKkut</v31:correlationID>
                    <v31:status>OK</v31:status>
                    <v31:code>Successfully-001</v31:code>
                    <v31:description>successfully.</v31:description>
                </v31:GeneralResponse>
            </v31:ResponseHeader>
            <v11:responseBody>
                <v11:msisdn> 149012098788</v11:msisdn>
                <v11:customer>
                    <v12:name xmlns:v12="http://xmlns.fl.com/CustomerType/V1">FL Company</v12:name>
                    <v12:clientCode xmlns:v12="http://xmlns.fl.com/CustomerType/V1">5690001212</v12:clientCode>
                    <v12:firstName xmlns:v12="http://xmlns.fl.com/CustomerType/V1">FL Company</v12:firstName>
                    <v12:lastName xmlns:v12="http://xmlns.fl.com/CustomerType/V1">RA</v12:lastName>
                    <v12:birthDate xmlns:v12="http://xmlns.fl.com/CustomerType/V1">1997-08-07</v12:birthDate>
                    <v12:gender xmlns:v12="http://xmlns.fl.com/CustomerType/V1">Female</v12:gender>
                    <v12:address xmlns:v12="http://xmlns.fl.com/CustomerType/V1">[email protected]</v12:address>
                    <v12:email xmlns:v12="http://xmlns.fl.com/CustomerType/V1">[email protected]</v12:email>
                    <v12:nationality xmlns:v12="http://xmlns.fl.com/CustomerType/V1">233</v12:nationality>
                    <v12:clientGrade xmlns:v12="http://xmlns.fl.com/CustomerType/V1">CustomerLevel2</v12:clientGrade>
                    <v12:clientType xmlns:v12="http://xmlns.fl.com/CustomerType/V1">Company</v12:clientType>
                    <v12:clientState xmlns:v12="http://xmlns.fl.com/CustomerType/V1">Unspecified</v12:clientState>
                    <v12:admissionDate xmlns:v12="http://xmlns.fl.com/CustomerType/V1">2022-07-16T08:19:20</v12:admissionDate>
                    <v12:additionalProperties xmlns:v12="http://xmlns.fl.com/CustomerType/V1">
                        <v2:ParameterType>
                            <v2:parameterName>Value</v2:parameterName>
                            <v2:parameterValue>0.0</v2:parameterValue>
                        </v2:ParameterType>
                    </v12:additionalProperties>
                </v11:customer>
                <v11:subscriber>
                    <v12:imsi xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">990423434534</v12:imsi>
                    <v12:clientCode xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">7870559304234</v12:clientCode>
                    <v12:brandId xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">120</v12:brandId>
                    <v12:language xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">English</v12:language>
                    <v12:smsLanguage xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">English</v12:smsLanguage>
                    <v12:ussdLanguage xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">English</v12:ussdLanguage>
                    <v12:customerType xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">Foreign</v12:customerType>
                    <v12:initialCredit xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">30</v12:initialCredit>
                    <v12:mainProductID xmlns:v12="http://xmlns.fl.com/SubscriberType/V1">990877687</v12:mainProductID>
                </v11:subscriber>
            </v11:responseBody>
        </v11:InfoResponse>
    </soapenv:Body>
</soapenv:Envelope>';

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.xmlsoap.org/soap/envelope/'
    , 'http://xmlns.fl.com/InfoResponse/V1' AS v11
    , 'http://xmlns.fl.com/CustomerType/V1' AS v12
    , 'http://xmlns.fl.com/SubscriberType/V1' AS v121) 
SELECT r.value('(v11:msisdn/text())[1]','varchar(50)') AS msisdn
    , c.value('(v12:name/text())[1]','varchar(50)') AS custname
    , c.value('(v12:clientCode/text())[1]','varchar(50)') AS clientCode
    , s.value('(v121:customerType/text())[1]','varchar(50)') AS customerType
    , s.value('(v121:initialCredit/text())[1]','INT') AS initialCredit
FROM @XML.nodes('/Envelope/Body/v11:InfoResponse/v11:responseBody') AS t1(r)
    CROSS APPLY t1.r.nodes('v11:customer') AS t2(c)
    CROSS APPLY t1.r.nodes('v11:subscriber') AS t3(s);

Output

 --------------- ------------ ------------ -------------- --------------- 
|    msisdn     |  custname  | clientCode | customerType | initialCredit |
 --------------- ------------ ------------ -------------- --------------- 
|  149012098788 | FL Company | 5690001212 | Foreign      |            30 |
 --------------- ------------ ------------ -------------- ---------------