crompnk
asked on
parsing xml using tsql and http response
Hi,
I'd like to parse some xml from a URL but without having to create a temporary table i.e. #xml, is this possible, I've tried with code below, which runs ok but doesn't return any values.
Thank you
I'd like to parse some xml from a URL but without having to create a temporary table i.e. #xml, is this possible, I've tried with code below, which runs ok but doesn't return any values.
Thank you
--IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml
--CREATE TABLE #xml ( yourXML XML )
--GO
DECLARE @URL VARCHAR(8000)
DECLARE @QS varchar(50)
-- & or ? depending if there are other query strings
-- Use this for when there is other query strings:
SELECT @QS = '&date='+convert(varchar(25),getdate(),126)
-- Use this for when there is NO other query strings:
-- SELECT @QS = '?date='+convert(varchar(25),getdate(),126)
SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?latlng=10.247087,-65.598409&sensor=false' + @QS
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
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml', @Response OUT
SET @XML = @Response
SELECT T.A.value('status[1]','VARCHAR(MAX)')
FROM @XML.nodes('GeocodeResponse/status') AS T(A)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.