(SQL Server) Send XMLHttpRequest and Get Response

Demonstrates how to send an HTTP XML request and retrieve the response.

Download Chilkat HTTP ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    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

    --  The XML sent in the body of the request is application
    --  specified -- it should contain whatever the server-side
    --  application is expecting..
    DECLARE @xmlStr nvarchar(4000)

    SELECT @xmlStr = '<?xml version="1.0" encoding="UTF-8"?><appXml><something>123</something></appXml>'

    --  The PostXml method may be called to send the XMLHttpRequest
    --  using the "POST" HTTP method (also known as "verb").
    DECLARE @urlEndpoint nvarchar(4000)

    SELECT @urlEndpoint = 'http://www.chilkatsoft.com/xmlEcho.asp'
    DECLARE @xmlCharset nvarchar(4000)

    SELECT @xmlCharset = 'utf-8'

    DECLARE @response int

    EXEC sp_OAMethod @http, 'PostXml', @response OUT, @urlEndpoint, @xmlStr, @xmlCharset
    IF @response Is NULL 
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END

    --  The raw HTTP request sent by the above call is:
    --  
POST /xmlEcho.asp HTTP/1.1
Content-Type: text/xml
User-Agent: Chilkat/1.0.0 (+http://www.chilkatsoft.com/ChilkatHttpUA.asp)
Host: www.chilkatsoft.com
Accept: */*
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*; q=0.7
Accept-Language: en-us,en;q=0.5
Accept-Encoding: gzip
Content-Length: 81

<?xml version="1.0" encoding="UTF-8"?><appXml><something>123</something></appXml>


    --  Examine the Body of the HTTP response
    --  This would be the XML response sent by the server.
    EXEC sp_OAGetProperty @response, 'BodyStr', @sTmp0 OUT

    PRINT @sTmp0


END
GO