How do I use a stored procedure in SQL Server 2014 to call a external XML source? I am attempting to retrieve currency exchange rates from http://www.floatrates.com/daily/usd.xml using a stored procedure in SQL Server 2014. The result is in XML. So I created the following stored procedure:

--DROP PROCEDURE [dbo].[proc_CallWebService]
CREATE PROCEDURE [dbo].[proc_CallWebService]
AS
    DECLARE @obj INT
    DECLARE @sURL VARCHAR(200)
    DECLARE @response varchar(max)

    SET @sURL = 'http://www.floatrates.com/daily/usd.xml'

    EXEC sys.sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
    EXEC sys.sp_OAMethod @obj, 'Open', NULL, 'GET', @sURL, FALSE
    EXEC sys.sp_OAMethod @obj, 'send'
    EXEC sys.sp_OAGetProperty @obj, 'responseText', @response OUT

    SELECT @response [response]
    EXEC sys.sp_OADestroy @obj
RETURN


and when I run this stored procedure, I get NULL in return instead of the XML.

What do I need to change in my stored procedure to get an XML response from that site mentioned above?
EXEC sys.sp_OAGetProperty @obj, 'responseText', @response OUT 

sometimes doesn't return the output parameter.  Particularly if the response XML is longer than your @response variable.

Try:

declare  @result table(data text)

INSERT into @result (Data) exec sp_OAGetProperty @obj, 'responseText'

select * from @result