Calling Web Service from T-SQL

This script calculates calls a web service using T-SQL. To do this OLE Automation was used.  The ServerXMLHTTP.6.0 object is used to accomplish this.  See http://support.microsoft.com/kb/290761 for more information about this object.


Old Method :
 

DECLARE @URI varchar(8000),
  @output_XML varchar(8000),
  @result int,
  @object int
set @URI = 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT'
EXEC @result = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @object OUTPUT
IF @result <> 0
BEGIN
  RAISERROR('sp_OACreate on MSXML2.XMLHttp.6.0 failed', 16,1)
  RETURN
END
EXEC @result = sp_OAMethod @object, 'open', NULL, 'GET', @URI, false
IF @result <>0
BEGIN
  RAISERROR('sp_OAMethod Open failed', 16,1)
  RETURN
END
EXEC @result = sp_OAMethod @object, SEND, NULL, ''
IF @result <>0
BEGIN
  RAISERROR('sp_OAMethod SEND failed', 16,1)
  RETURN
END

EXEC @result = sp_OAGetProperty @object, 'responseText', @output_XML OUTPUT
IF @result <>0 
BEGIN
  RAISERROR('sp_OAGetProperty responseText failed', 16,1)
  RETURN
END
 
SELECT @output_XML StockQouteXML

EXEC @result = sp_OADestroy @object
IF @result <>0 
BEGIN
  RAISERROR('sp_OAGetProperty responseText failed', 16,1)
  RETURN
END