locked
Trying to obtain XML data from URL via SQL RRS feed

  • Question

  • Hi everybody.

    I have this code:

    Declare @Object as Int;
    Declare @ResponseText as Varchar(8000);
    Declare @Url as Varchar(MAX);
    select @Url = 'http://maps.google.com/maps/api/geocode/xml?latlng=10.247087,-67.598409&sensor=false'
    --select @Url = 'http://ws.geonames.org/countryCode?lat=' + CAST(40.417 as varchar) + '&lng='+ cast(-3.703 as varchar) +'&type=xml'

    Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
    Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false'
    Exec sp_OAMethod @Object, 'send'
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
    Exec sp_OADestroy @Object

    Declare @XmlResponse as xml;
    select @XmlResponse = CAST(@ResponseText as xml)
    select @XmlResponse.value('(/GeocodeResponse/status)[1]','varchar(50)') as CountryName

    dont generate results. if i remove the "&sensor=off" from the url, returns a value. What i doing wrong?

    Any help is appreciated.

     


    Bernardo Salazar
    Saturday, December 3, 2011 3:25 PM

Answers

  • I used dbo.sp_DisplayOAErrorInfo to get the error message:
    The source data specified for this string or binary column or parameter is too long.

    As you are using VARCHAR(MAX) you must be using SQL 2005 or greater.  You can work around this limitation by inserting the results into a temp table with an XML column, eg

    USE tempdb
    GO
    
    IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml
    CREATE TABLE #xml ( yourXML XML )
    GO
    
    DECLARE @URL VARCHAR(8000) 
    
    SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?latlng=10.247087,-65.598409&sensor=false'	-- This works
    SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?latlng=10.247087,-67.598409&sensor=false'	-- This doesn't as string is too long
    --SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?latlng=10.247087,-67.598409'
    
    DECLARE @Response varchar(8000)
    DECLARE @XML xml
    DECLARE @Obj int 
    DECLARE @Result int 
    DECLARE @HTTPStatus int 
    DECLARE @ErrorMsg varchar(MAX)
    
    EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT 
    
    EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
    EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
    EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
    EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 
    
    INSERT #xml ( yourXML )
    EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT 
    
    SELECT *
    FROM #xml
    
    SELECT x.*, y.c.query('.')
    FROM #xml x
    	CROSS APPLY x.yourXML.nodes('/GeocodeResponse/status') y(c)
    

    Monday, December 5, 2011 12:07 AM
    Answerer

All replies

  • I used dbo.sp_DisplayOAErrorInfo to get the error message:
    The source data specified for this string or binary column or parameter is too long.

    As you are using VARCHAR(MAX) you must be using SQL 2005 or greater.  You can work around this limitation by inserting the results into a temp table with an XML column, eg

    USE tempdb
    GO
    
    IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml
    CREATE TABLE #xml ( yourXML XML )
    GO
    
    DECLARE @URL VARCHAR(8000) 
    
    SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?latlng=10.247087,-65.598409&sensor=false'	-- This works
    SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?latlng=10.247087,-67.598409&sensor=false'	-- This doesn't as string is too long
    --SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?latlng=10.247087,-67.598409'
    
    DECLARE @Response varchar(8000)
    DECLARE @XML xml
    DECLARE @Obj int 
    DECLARE @Result int 
    DECLARE @HTTPStatus int 
    DECLARE @ErrorMsg varchar(MAX)
    
    EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT 
    
    EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
    EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
    EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
    EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 
    
    INSERT #xml ( yourXML )
    EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT 
    
    SELECT *
    FROM #xml
    
    SELECT x.*, y.c.query('.')
    FROM #xml x
    	CROSS APPLY x.yourXML.nodes('/GeocodeResponse/status') y(c)
    

    Monday, December 5, 2011 12:07 AM
    Answerer
  • Hi wBob!

    A million thanks for your response, it really helped me A LOT. (im still a newbie in SQL)

    Just to understand the code, the result that i obtain from the URL is larger than 8000 characters, then, @ResponseText fail to get all data, and XML structure finish incomplete, giving as result a NULL when i try to make a SELECT. right?

    In the INSERT #xml ( yourXML ) EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml' command you're inserting the result directly to a temp table (#xml), and later i make SELECT statements against the temp table. Its all correct?


    Again, many thanks!

    Greetings from Venezuela.


    Bernardo Salazar
    Monday, December 5, 2011 2:20 PM