(SQL Server) Making SOAP Requests using the REST API

There's no reason a SOAP request cannot be sent using the REST API. It's only a matter of adding a SOAP specific header, and providing the SOAP XML request body. The response body is the SOAP response XML.

This example sends a request to ws.cdyne.com to get city weather by zip code.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- Demonstrates how to do a SOAP request using the REST API.

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- --------------------------------------------------------------------------------
    -- Also see Chilkat's Online WSDL Code Generator
    -- to generate code and SOAP Request and Response XML for each operation in a WSDL.
    -- --------------------------------------------------------------------------------

    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- This is the request to be sent:

    -- POST /WeatherWS/Weather.asmx HTTP/1.1
    -- Host: wsf.cdyne.com
    -- Content-Type: text/xml; charset=utf-8
    -- Content-Length: length
    -- SOAPAction: "http://ws.cdyne.com/WeatherWS/GetCityWeatherByZIP"
    -- 
    -- <?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>
    --     <GetCityWeatherByZIP xmlns="http://ws.cdyne.com/WeatherWS/">
    --       <ZIP>string</ZIP>
    --     </GetCityWeatherByZIP>
    --   </soap:Body>
    -- </soap:Envelope>

    -- Note: The application does not need to set the Host or Content-Length headers.
    -- Chilkat will automatically add these headers.

    -- Make the initial connection (without sending a request yet).
    DECLARE @bTls int
    SELECT @bTls = 0
    DECLARE @port int
    SELECT @port = 80
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    -- In this particular case, it is important to connect to "wsf.cdyne.com", not "ws.cdyne.com"...
    DECLARE @success int
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'wsf.cdyne.com', @port, @bTls, @bAutoReconnect
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- Add request headers:
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'text/xml; charset=utf-8'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'SOAPAction', 'http://ws.cdyne.com/WeatherWS/GetCityWeatherByZIP'

    -- Build the SOAP XML request body.
    DECLARE @soapXml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @soapXml OUT

    EXEC sp_OASetProperty @soapXml, 'Tag', 'soap:Envelope'
    EXEC sp_OAMethod @soapXml, 'AddAttribute', @success OUT, 'xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance'
    EXEC sp_OAMethod @soapXml, 'AddAttribute', @success OUT, 'xmlns:xsd', 'http://www.w3.org/2001/XMLSchema'
    EXEC sp_OAMethod @soapXml, 'AddAttribute', @success OUT, 'xmlns:soap', 'http://schemas.xmlsoap.org/soap/envelope/'

    EXEC sp_OAMethod @soapXml, 'NewChild2', NULL, 'soap:Body', ''
    EXEC sp_OAMethod @soapXml, 'GetChild2', @success OUT, 0
    EXEC sp_OAMethod @soapXml, 'NewChild2', NULL, 'GetCityWeatherByZIP', ''
    EXEC sp_OAMethod @soapXml, 'GetChild2', @success OUT, 0
    EXEC sp_OAMethod @soapXml, 'AddAttribute', @success OUT, 'xmlns', 'http://ws.cdyne.com/WeatherWS/'
    EXEC sp_OAMethod @soapXml, 'NewChild2', NULL, 'ZIP', '60187'
    EXEC sp_OAMethod @soapXml, 'GetRoot2', NULL

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

    -- Send the SOAP request
    DECLARE @responseXml nvarchar(4000)
    EXEC sp_OAMethod @soapXml, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @rest, 'FullRequestString', @responseXml OUT, 'POST', '/WeatherWS/Weather.asmx', @sTmp0
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @soapXml
        RETURN
      END

    -- When successful, the response status code will equal 200.
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        -- Examine the request/response to see what happened.

        EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
        PRINT 'response status code = ' + @iTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
        PRINT 'response status text = ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
        PRINT 'response header: ' + @sTmp0

        PRINT 'response body (if any): ' + @responseXml

        PRINT '---'

        EXEC sp_OAGetProperty @rest, 'LastRequestStartLine', @sTmp0 OUT
        PRINT 'LastRequestStartLine: ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
        PRINT 'LastRequestHeader: ' + @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @soapXml
        RETURN
      END

    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @responseXml

    -- GetXml will emit XML that is nicely indented for human viewing..
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- A sample response XML is shown below...

    -- To get some information, use ChilkatPath.  For example...

    EXEC sp_OAMethod @xml, 'ChilkatPath', @sTmp0 OUT, 'soap:Body|GetCityWeatherByZIPResponse|GetCityWeatherByZIPResult|Temperature|*'
    PRINT 'Temperature: ' + @sTmp0


    PRINT 'Success.'

    -- <?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>
    --         <GetCityWeatherByZIPResponse xmlns="http://ws.cdyne.com/WeatherWS/">
    --             <GetCityWeatherByZIPResult>
    --                 <Success>true</Success>
    --                 <ResponseText>City Found</ResponseText>
    --                 <State>IL</State>
    --                 <City>Wheaton</City>
    --                 <WeatherStationCity>West Chicago</WeatherStationCity>
    --                 <WeatherID>10</WeatherID>
    --                 <Description>Mostly Sunny</Description>
    --                 <Temperature>33</Temperature>
    --                 <RelativeHumidity>39</RelativeHumidity>
    --                 <Wind>VRB5</Wind>
    --                 <Pressure>30.33F</Pressure>
    --                 <Visibility />
    --                 <WindChill />
    --                 <Remarks />
    --             </GetCityWeatherByZIPResult>
    --         </GetCityWeatherByZIPResponse>
    --     </soap:Body>
    -- </soap:Envelope>

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @soapXml
    EXEC @hr = sp_OADestroy @xml


END
GO