Share via

How to parse SOAP XML in SQL Server and show as table data

  • Article
  • 05/14/2019

Question

Tuesday, May 14, 2019 2:12 PM

Hi I have sample data in SOAP XML

DECLARE@xml XML=
'<soapenv:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">    <soap:Header xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">        <ns0:Context xmlns:ns0="http://service.PUBG.com/entity/message/2007/" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">            <messageId>                <N455></N455>            </messageId>            <refToMessageId>142323</refToMessageId>            <Timestamp>2018-08-08T23:00:11.282Z</Timestamp>            <applicationId>PUBG</applicationId>            <hostName>UNKNOWN</hostName>            </ns0:Context>    </soap:Header>    <soap:Body xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">        <ns2:searchResponseResponse xmlns:ns2="http://service.PubG.com/provider/gpow/bankLookup/PubGLookUpService/2013/" xmlns:ns4="http://service.PubG.com/provider/gpow/bankLookup/PubGLookUpService/2015/">            <ns4:PubGDetails>                <ns4:Name>PUBG Roll</ns4:Name>            </ns4:PubGDetails>            <ns4:PubGCode>xxxxxxx</ns4:PubGCode>            <ns4:countryCode>US</ns4:countryCode>            <ns4:address>                <ns4:physicalAddress1>XXX PARK AVENUE</ns4:physicalAddress1>            </ns4:address>            <ns4:zipCode>123456</ns4:zipCode>            <ns4:countryName>USA</ns4:countryName>            <ns4:parentPubGCode>0</ns4:parentPubGCode>            <ns4:modificationInd>A</ns4:modificationInd>            <ns4:modificationDate>2017-05-02</ns4:modificationDate>        </ns2:searchResponseResponse>    </soap:Body></soapenv:Envelope>';

I have written code like this  to get in table format  :

SELECTr.value('messageId[1]','varchar(max)')ASmessageId      ,r.value('refToMessageId[1]','varchar(max)')ASrefToMessageId      ,r.value('Timestamp[1]','datetime')ASTimestamp
      ,r.value('applicationId[1]','varchar(max)')ASapplicationId      ,r.value('hostName[1]','varchar(max)')AShostName      ,r.value('PubGDetails[1]','varchar(max)')ASPubGDetails      ,r.value('CityHeading[1]','varchar(max)')ASCityHeading      ,r.value('PubGCode[1]','varchar(max)')ASPubGCode      ,r.value('countryCode[1]','varchar(max)')AScountryCode      ,r.value('PubGId[1]','int')ASPubGCode      ,r.value('address[1]','varchar(max)')ASPubGCode      ,r.value('zipCode[1]','int')ASPubGCode      ,r.value('countryName[1]','varchar(max)')ASPubGCode      ,r.value('parentPubGCode[1]','int')ASPubGCode      ,r.value('modificationInd[1]','varchar(2)')ASPubGCode      ,r.value('modificationDate[1]','DateTime')ASPubGCode
FROM@xml.nodes('/*:Envelope/*:Body/*:row')ASA(r)

How to get output like this :

messageId refToMessageId Timestamp applicationId hostName PubGDetails PubGCode countryCode address zipCode countryName parentPubGCode modificationInd modificationDate
N455D 142323 8/8/2018 PUBG UNKNOWN PUBG Roll xxxxxxxxx us XXX PARK AVENUE 123456 USA 0 A 5/2/2017

while executing i'm getting empty data set

All replies (5)

Tuesday, May 14, 2019 2:51 PM

Good day mohan,

Your question is not related to SQL Server but to XML format.  your text is not a valid XML which lead to the issue in the SQL Server side.

First make sure that you are using valid XML using one of the online Formatter like this one.

Here are some issue in your text which make is NON-XML content:

<soapenv:Envelope  xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"

You forgot to close the tag with ">"

You have this tag: <N455d> without a closer </N455d>

<ns4:Name> without closer

and so on, and so on....

 

Note! You can use TRY_CONVERT in order to confirm if the text is valud XML but thuis will not give you the reasons of the issue but only a "mark" that the text is not valid to convert to XML

DECLARE @string NVARCHAR(MAX)=
'<soapenv:Envelope
 xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
 xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
 <soap:Header xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <ns0:Context xmlns:ns0="http://service.PUBG.com/entity/message/2007/">
 xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
 xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
 <messageId><N455d></messageId>
 <refToMessageId>142323</refToMessageId>
 <Timestamp>2018-08-08T23:00:11.282Z</Timestamp>
 <applicationId>PUBG</applicationId>
 <hostName>UNKNOWN</hostName>
 </ns0:Context></soap:Header>
 <soap:Body xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <ns2:searchResponseResponse xmlns:ns2="http://service.PubG.com/provider/gpow/bankLookup/PubGLookUpService/2013/">
 <ns4:PubGDetails><ns4:Name>PUBG Roll</ns4:PubGDetails>
 <ns4:PubGCode>xxxxxxx</ns4:PubGCode>
 <ns4:countryCode>US</ns4:countryCode>
 <ns4:address><ns4:physicalAddress1>XXX PARK AVENUE</ns4:physicalAddress1></ns4:address>
 <ns4:zipCode>123456</ns4:zipCode>
 <ns4:countryName>USA</ns4:countryName>
 <ns4:parentPubGCode>0</ns4:parentPubGCode>
 <ns4:modificationInd>A</ns4:modificationInd>
 <ns4:modificationDate>2017-05-02</ns4:modificationDate>
 </ns2:searchPubGResponse>
 </soap:Body>
 </soapenv:Envelope>';
 select TRY_CONVERT (XML, @String)
  Ronen Ariely
 [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Tuesday, May 14, 2019 4:01 PM

Hi Ariely,

I have modified the question can you please have look on that now

Tuesday, May 14, 2019 10:06 PM | 1 vote

Hi mohan1111,

As Ronen pointed out, the XML was not well-formed. The second issue with SQL was related to the namespace declarations and XPath expressions. Here is your solution:

DECLARE @xml XML = '<soapenv:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soap:Header xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
        <ns0:Context xmlns:ns0="http://service.PUBG.com/entity/message/2007/" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
            <messageId>N455D</messageId>
            <refToMessageId>142323</refToMessageId>
            <Timestamp>2018-08-08T23:00:11.282Z</Timestamp>
            <applicationId>PUBG</applicationId>
            <hostName>UNKNOWN</hostName>
        </ns0:Context>
    </soap:Header>
    <soap:Body xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
        <ns2:searchResponseResponse xmlns:ns2="http://service.PubG.com/provider/gpow/bankLookup/PubGLookUpService/2013/" xmlns:ns4="http://service.PubG.com/provider/gpow/bankLookup/PubGLookUpService/2015/">
            <ns4:PubGDetails>
                <ns4:Name>PUBG Roll</ns4:Name>
            </ns4:PubGDetails>
            <ns4:PubGCode>xxxxxxx</ns4:PubGCode>
            <ns4:countryCode>US</ns4:countryCode>
            <ns4:address>
                <ns4:physicalAddress1>XXX PARK AVENUE</ns4:physicalAddress1>
            </ns4:address>
            <ns4:zipCode>123456</ns4:zipCode>
            <ns4:countryName>USA</ns4:countryName>
            <ns4:parentPubGCode>0</ns4:parentPubGCode>
            <ns4:modificationInd>A</ns4:modificationInd>
            <ns4:modificationDate>2017-05-02</ns4:modificationDate>
        </ns2:searchResponseResponse>
    </soap:Body>
</soapenv:Envelope>';

;WITH XMLNAMESPACES ('http://schemas.xmlsoap.org/soap/envelope/' AS soap,
    'http://service.PubG.com/provider/gpow/bankLookup/PubGLookUpService/2013/' AS ns2,
    'http://service.PubG.com/provider/gpow/bankLookup/PubGLookUpService/2015/' AS ns4,
    'http://service.PUBG.com/entity/message/2007/' as ns0), rs AS
(
   SELECT col.value('(soap:Header/ns0:Context/messageId)[1]','VARCHAR(20)') AS messageId
   , col.value('(soap:Header/ns0:Context/refToMessageId)[1]','VARCHAR(20)') AS refToMessageId
   , col.value('(soap:Header/ns0:Context/Timestamp)[1]','DATE') AS [Timestamp]
   , col.value('(soap:Header/ns0:Context/applicationId)[1]','VARCHAR(20)') AS applicationId
   , col.value('(soap:Header/ns0:Context/hostName)[1]','VARCHAR(20)') AS hostName
   , col.value('(soap:Body/ns2:searchResponseResponse/ns4:PubGDetails/ns4:Name)[1]','VARCHAR(20)') AS PubGDetails
   , col.value('(soap:Body/ns2:searchResponseResponse/ns4:PubGCode)[1]','VARCHAR(20)') AS PubGCode
   , col.value('(soap:Body/ns2:searchResponseResponse/ns4:countryCode)[1]','VARCHAR(20)') AS countryCode
   , col.value('(soap:Body/ns2:searchResponseResponse/ns4:address/ns4:physicalAddress1)[1]','VARCHAR(20)') AS [Address]
   , col.value('(soap:Body/ns2:searchResponseResponse/ns4:zipCode)[1]','VARCHAR(20)') AS zipCode
   , col.value('(soap:Body/ns2:searchResponseResponse/ns4:countryName)[1]','VARCHAR(20)') AS countryName
   , col.value('(soap:Body/ns2:searchResponseResponse/ns4:parentPubGCode)[1]','VARCHAR(20)') AS parentPubGCode
   , col.value('(soap:Body/ns2:searchResponseResponse/ns4:modificationInd)[1]','VARCHAR(20)') AS modificationInd
   , col.value('(soap:Body/ns2:searchResponseResponse/ns4:modificationDate)[1]','DATE') AS modificationDate
   FROM @xml.nodes('/soap:Envelope') AS tab(col)
)
SELECT * FROM rs;

Output:

messageId    refToMessageId  Timestamp   applicationId   hostName    PubGDetails PubGCode    countryCode Address zipCode countryName parentPubGCode  modificationInd modificationDate
N455D   142323  2018-08-08  PUBG    UNKNOWN PUBG Roll   xxxxxxx US  XXX PARK AVENUE 123456  USA 0   A   2017-05-02

Wednesday, May 15, 2019 9:03 AM

thank you khabinsky...very well answered even if my question is not well foramtted

Wednesday, May 15, 2019 2:42 PM

Hi mohan1111,

Glad to hear that the proposed solution is working for you.

There is a dedicated forum for SQL Server XML questions.

Here is the link: SQL Server XML forum

Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.