Parse SOAP XML Response

Code to parse a SOAP XML response.

This is the SOAP response parsed by the code below:

<?xml version="1.0"?>
<soap:Envelope
xmlns:soap="http://www.w3.org/2001/12/soap-envelope"
soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding">

<soap:Body xmlns:m="http://www.example.org/stock">
  <m:GetStockPriceResponse>
    <m:Price>34.5</m:Price>
  </m:GetStockPriceResponse>
</soap:Body>

Download Chilkat XML ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int

    DECLARE @bFound int

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  Load the XML object with the SOAP response.
    --  The XML may be loaded from a file by calling
    --  LoadXmlFile, or directly from a string by calling
    --  LoadXml.
    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'soapResponse.xml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END

    --  The quickest way to get a piece of data is w/ the
    --  ChilkatPath method.
    --  To get the content of the m:Price node:
    DECLARE @strPrice nvarchar(4000)

    EXEC sp_OAMethod @xml, 'ChilkatPath', @strPrice OUT, 'soap:Body|m:GetStockPriceResponse|m:Price|*'


    PRINT 'Price = ' + @strPrice

    --  ------------------------------------
    --  Another way of doing it:

    --  Another way to get it is to navigate to the node.
    --  Navigate to soap:Body
    EXEC sp_OAMethod @xml, 'FirstChild2', NULL

    --  Navigate to m:GetStockPriceResponse
    --  You may call FirstChild2, or you may call GetChildWithTag2
    --  to get the Nth direct child having a given tag.
    --  In this case, get the 1st direct child having the tag
    --  "m:GetStockPriceResponse"
    --  (Indexing begins at 0, so the 1st occurance is at index 0.)
    EXEC sp_OAMethod @xml, 'GetNthChildWithTag2', @bFound OUT, 'm:GetStockPriceResponse', 0
    IF @bFound <> 1
      BEGIN

        PRINT 'Did not find m:GetStockPriceResponse node.'
        RETURN
      END

    --  One more to go..
    EXEC sp_OAMethod @xml, 'GetNthChildWithTag2', @bFound OUT, 'm:Price', 0
    IF @bFound <> 1
      BEGIN

        PRINT 'Did not find m:Price node.'
        RETURN
      END

    --  The content of the "m:Price" node is "34.5"
    EXEC sp_OAGetProperty @xml, 'Content', @strPrice OUT



    PRINT 'Price = ' + @strPrice

    --  Restore the xml object's internal reference to the root
    --  of the XML document:
    EXEC sp_OAMethod @xml, 'GetRoot2', NULL

    --  ------------------------------------
    --  Another way of doing it:

    --  If you know that the node w/ the content you want to
    --  extract is uniquely tagged (i.e. no other node has the same
    --  tag), then search for the node having the tag:
    EXEC sp_OAMethod @xml, 'SearchForTag2', @bFound OUT, @xml, 'm:Price'
    IF @bFound <> 1
      BEGIN
        --  Not found...
        RETURN
      END

    --  The content of the "m:Price" node is "34.5"
    EXEC sp_OAGetProperty @xml, 'Content', @strPrice OUT



    PRINT 'Price = ' + @strPrice

    --  Restore the xml object's internal reference to the root
    --  of the XML document:
    EXEC sp_OAMethod @xml, 'GetRoot2', NULL

    --  ------------------------------------
    --  Another way of doing it:

    --  Again, assuming there is only a single node with the
    --  tag to be fetched:
    EXEC sp_OAMethod @xml, 'AccumulateTagContent', @strPrice OUT, 'm:Price', ''


    PRINT 'Price = ' + @strPrice

    --  There are many other possible ways of doing the same thing...

END
GO