Trying to obtain XML data from URL via SQL

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

Answer 1

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)

Answer 2

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