Parse SOAP XML

Demonstrates how to parse SOAP XML.

Download Chilkat XML ActiveX

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

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

    --  The Chilkat XML component is freeware.

    --  Load an XML document.
    --  This document may be downloaded at:
    --  http://www.chilkatsoft.com/testData/soap1.xml
    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'soap1.xml'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  The sample XML document parsed here is a prime example
    --  of how NOT to design your XML schema.  When designing
    --  XML, always keep in mind how a program might parse it.
    --  Poorly designed XML is a big pain-in-the-ass to parse.
    --  We'll show you a much better schema at the end of this
    --  example.

    --  Here's the XML we'll want to parse:

    --  <?xml version="1.0" encoding="utf-8"?><soap:Envelope
    --  xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
    --  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    --  xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    --  <soap:Body>
    --  <SendRequestResponse
    --  xmlns="http://merchantwarehouse.com/webservices/public/MerchantWareService1">
    --  <SendRequestResult>
    --  <Code>Approved</Code>
    --  <ProcessorData>[Response_Code][00][Response_Text][106857][Auth_Reference
    --  ][0016][Amount_Balance][205.78][Expiration_Date][012014][Trans_Date_Time
    --  ][012209000734][Card_Number][710001900593731][Transaction_ID][1]</ProcessorData>
    --  <Fields><Field>
    --  <Code>Unknown</Code><Value>1</Value>
    --  </Field>
    --  <Field>
    --  <Code>Message</Code><Value>106857</Value>
    --  </Field>
    --  <Field>
    --  <Code>AuthCodeResp</Code><Value>0016</Value>
    --  </Field>
    --  <Field>
    --  <Code>Balance</Code><Value>205.78</Value>
    --  </Field>
    --  </Fields>
    --  </SendRequestResult>
    --  </SendRequestResponse>
    --  </soap:Body>
    --  </soap:Envelope>

    --  The poor design choice in the above XML is to use the same tag over and over.
    --  For example "Field", "Code", and "Value".
    --  Here's a much much better design, and you'll see later how the parsing is so much simpler:
    -- 
    --  ...
    --  <Fields>
    --  <Message>106857</Message>
    --  <AuthCodeResp>0016</AuthCodeResp>
    --  <Balance>205.78</Balance>
    --  </Fields>
    --  ...

    --  Notice it's much more compact.
    --  The other poor design choice is globbing all of that information in "ProcessorData".
    --  You can't use XML alone to parse the contents of that node -- you'll have to write
    --  some custom string parsing code for it...  (ughhh)
    -- 

    --  OK... so let's parse this beast...
    --  We already have it loaded in an instance of the XML object.
    --  Drill down to the SendRequestResult node:
    EXEC sp_OAMethod @xml, 'FirstChild2', NULL
    EXEC sp_OAMethod @xml, 'FirstChild2', NULL
    EXEC sp_OAMethod @xml, 'FirstChild2', NULL

    --  Find the "Fields" node:
    DECLARE @xmlFields int

    EXEC sp_OAMethod @xml, 'GetChildWithTag', @xmlFields OUT, 'Fields'

    --  Iterate over each field:
    DECLARE @i int

    DECLARE @xmlField int

    DECLARE @numChildren int

    EXEC sp_OAGetProperty @xmlFields, 'NumChildren', @numChildren OUT

    SELECT @i = 0
    WHILE @i <= @numChildren - 1
      BEGIN

        EXEC sp_OAMethod @xmlFields, 'GetChild', @xmlField OUT, @i

        EXEC sp_OAMethod @xmlField, 'HasChildWithTagAndContent', @sTmp0 OUT, 'Code', 'Message'        IF @sTmp0 = 1
          BEGIN

            EXEC sp_OAMethod @xmlField, 'GetChildContent', @sTmp0 OUT, 'Value'
            PRINT 'Message: ' + @sTmp0
          END
        --  Use the same logic for the other fields you want to capture...

        SELECT @i = @i + 1
      END

    --  Here's the XML parsing code if the XML were designed like this:
    --  ...
    --  <Fields>
    --  <Unknown>1</Unknown>
    --  <Message>106857</Message>
    --  <AuthCodeResp>0016</AuthCodeResp>
    --  <Balance>205.78</Balance>
    --  </Fields>
    --  ...
    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'soap2.xml'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    EXEC sp_OAMethod @xml, 'FirstChild2', NULL
    EXEC sp_OAMethod @xml, 'FirstChild2', NULL
    EXEC sp_OAMethod @xml, 'FirstChild2', NULL

    --  Find the "Fields" node:
    EXEC sp_OAMethod @xml, 'GetChildWithTag', @xmlFields OUT, 'Fields'

    --  Get each value:

    PRINT '-----'

    EXEC sp_OAMethod @xmlFields, 'GetChildContent', @sTmp0 OUT, 'Message'
    PRINT 'Message: ' + @sTmp0

    EXEC sp_OAMethod @xmlFields, 'GetChildContent', @sTmp0 OUT, 'AuthCodeResp'
    PRINT 'AuthCodeResp:' + @sTmp0

    EXEC sp_OAMethod @xmlFields, 'GetChildContent', @sTmp0 OUT, 'Balance'
    PRINT 'Balance: ' + @sTmp0


END
GO