SOAP Example

How to send a SOAP request and get the XML response.

Download Chilkat XML ActiveX

Download Chilkat HTTP ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @req int
    EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

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

    DECLARE @success int

    --  Any string unlocks the component for the 1st 30-days.
    EXEC sp_OAMethod @http, 'UnlockComponent', @success OUT, 'Anything for 30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  Build this XML SOAP request:
    --  <?xml version="1.0" encoding="utf-8"?>
    --  <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    --  xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    --  xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    --    <soap:Body>
    --      <GetQuote xmlns="http://www.webserviceX.NET/">
    --        <symbol>string</symbol>
    --     </GetQuote>
    --    </soap:Body>
    --  </soap:Envelope>
    DECLARE @soapReq int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @soapReq OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @soapReq, 'Encoding', 'utf-8'
    EXEC sp_OASetProperty @soapReq, 'Tag', 'soap:Envelope'
    EXEC sp_OAMethod @soapReq, 'AddAttribute', NULL, 'xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance'
    EXEC sp_OAMethod @soapReq, 'AddAttribute', NULL, 'xmlns:xsd', 'http://www.w3.org/2001/XMLSchema'
    EXEC sp_OAMethod @soapReq, 'AddAttribute', NULL, 'xmlns:soap', 'http://schemas.xmlsoap.org/soap/envelope/'

    EXEC sp_OAMethod @soapReq, 'NewChild2', NULL, 'soap:Body', ''
    EXEC sp_OAMethod @soapReq, 'FirstChild2', NULL
    EXEC sp_OAMethod @soapReq, 'NewChild2', NULL, 'GetQuote', ''
    EXEC sp_OAMethod @soapReq, 'FirstChild2', NULL
    EXEC sp_OAMethod @soapReq, 'AddAttribute', NULL, 'xmlns', 'http://www.webserviceX.NET/'
    EXEC sp_OAMethod @soapReq, 'NewChild2', NULL, 'symbol', 'MSFT'
    EXEC sp_OAMethod @soapReq, 'GetRoot2', NULL

    EXEC sp_OAMethod @soapReq, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    --  Build an SOAP request.
    EXEC sp_OAMethod @soapReq, 'GetXml', @sTmp0 OUT    EXEC sp_OAMethod @req, 'UseXmlHttp', NULL, @sTmp0
    EXEC sp_OASetProperty @req, 'Path', '/stockquote.asmx'

    EXEC sp_OAMethod @req, 'AddHeader', NULL, 'SOAPAction', 'http://www.webserviceX.NET/GetQuote'

    --  Send the HTTP POST and get the response.  Note: This is a blocking call.
    --  The method does not return until the full HTTP response is received.
    DECLARE @domain nvarchar(4000)

    DECLARE @port int

    DECLARE @ssl int

    SELECT @domain = 'www.webservicex.net'
    SELECT @port = 80
    SELECT @ssl = 0
    DECLARE @resp int

    EXEC sp_OAMethod @http, 'SynchronousRequest', @resp OUT, @domain, @port, @ssl, @req
    IF @resp Is NULL 
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
      END
    ELSE
      BEGIN
        --  The XML response is in the BodyStr property of the response object:
        DECLARE @soapResp int
        EXEC @hr = sp_OACreate 'Chilkat.Xml', @soapResp OUT
        IF @hr <> 0
        BEGIN
            PRINT 'Failed to create ActiveX component'
            RETURN
        END

        EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
        EXEC sp_OAMethod @soapResp, 'LoadXml', NULL, @sTmp0

        --  The response will look like this:
        --  <?xml version="1.0" encoding="utf-8"?>
        --  <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        --  xmlns:xsd="http://www.w3.org/2001/XMLSchema"
        --  xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
        --    <soap:Body>
        --      <GetQuoteResponse xmlns="http://www.webserviceX.NET/">
        --        <GetQuoteResult>string</GetQuoteResult>
        --      </GetQuoteResponse>
        --    </soap:Body>
        --  </soap:Envelope>
        --  Navigate to soap:Body
        EXEC sp_OAMethod @soapResp, 'FirstChild2', NULL
        --  Navigate to GetQuoteResponse
        EXEC sp_OAMethod @soapResp, 'FirstChild2', NULL
        --  Navigate to GetQuoteResult
        EXEC sp_OAMethod @soapResp, 'FirstChild2', NULL

        --  The actual XML response is the data within GetQuoteResult:
        DECLARE @xmlResp int
        EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlResp OUT
        IF @hr <> 0
        BEGIN
            PRINT 'Failed to create ActiveX component'
            RETURN
        END

        EXEC sp_OAGetProperty @soapResp, 'Content', @sTmp0 OUT
        EXEC sp_OAMethod @xmlResp, 'LoadXml', NULL, @sTmp0

        --  Display the XML response:
        EXEC sp_OAMethod @xmlResp, 'GetXml', @sTmp0 OUT
        PRINT @sTmp0

      END
END
GO