Answered by:
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 @ObjectDeclare @XmlResponse as xml;
select @XmlResponse = CAST(@ResponseText as xml)
select @XmlResponse.value('(/GeocodeResponse/status)[1]','varchar(50)') as CountryNamedont generate results. if i remove the "&sensor=off" from the url, returns a value. What i doing wrong?
Any help is appreciated.
Bernardo SalazarSaturday, 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)
- Marked as answer by Bernardo Salazar Monday, December 5, 2011 2:06 PM
Monday, December 5, 2011 12:07 AMAnswerer
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)
- Marked as answer by Bernardo Salazar Monday, December 5, 2011 2:06 PM
Monday, December 5, 2011 12:07 AMAnswerer -
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 SalazarMonday, December 5, 2011 2:20 PM