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=40.4193371,-3.6952723&sensor=false'
DECLARE @Response varchar(MAX)
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'SELECT * FROM #xml
SELECT x.*, y.c.query('.') FROM #xml x CROSS APPLY x.yourXML.nodes('/GeocodeResponse/status') y(c)