1

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?

Improve this question
1

1 Answer 1

Reset to default
2

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

Improve this answer
6
  • Did not see this pattern explained anywhere else, but worked like a charm, at least for SqlServer 2014. I recommend using 'nvarchar(max)' instead of 'text'.
    – mdisibio
    Mar 4, 2016 at 0:08
  • @mdisibio nvarchar(max) can only contain 4000 characters, table(data text) made the trick, thank you @Bruce
    – Francesco Mantovani
    Oct 6, 2018 at 5:27
  • @FrancescoMantovani I agree. Varchar and nvarchar limits can be a challenge. I made that comment based on Microsoft's ongoing warning since SqlServer 2008: "ntext, text, and image data types will be removed ... Avoid using these data types ... Use nvarchar(max), varchar(max), and varbinary(max) instead." [docs.microsoft.com/en-us/sql/t-sql/data-types/…. I think the idea is that if you call an external xml source, it would be a reasonable sized message (REST maybe). Xml as a parameter type still supports large documents.
    – mdisibio
    Oct 8, 2018 at 16:56
  • @FrancescoMantovani. But re-reading your specific issue, you were already using varchar(max), so recommending nvarchar(max) is sensless...even less capacity. Sorry for the confusion. Was raising the flag about a data type that might get deprecated, as well as the fact that Sql Server - when not using the xml data type, usually handles xml as nvarchar.
    – mdisibio
    Oct 8, 2018 at 17:04
  • @mdisibio this article: stackoverflow.com/questions/22067593/… has more than 42k viewes. as you can see on the first response, on line 2 they use Declare @ResponseText as Varchar(8000); which was the maximum allowed. try to swap it with Declare @ResponseText as varchar(MAX); or Declare @ResponseText as nvarchar(MAX); and you will see it epically fail because Varchar(MAX); and Narchar(MAX); can only store till 4000 characters! Yes, you hear it well!
    – Francesco Mantovani
    Oct 9, 2018 at 0:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.