From SQL get a value from XML tag (xml over 8000 char)

DECLARE @URL varchar(MAX), @Distance varchar(40)
--Google V3--
 set @URL = 'http://maps.googleapis.com/maps/api/directions/xml?origin=Boston,MA&destination=Charleston,sc&sensor=false'

 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.ServerXMLHttp', @Obj OUT 
 BEGIN TRY
 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
 --print @Result
 END TRY
 BEGIN CATCH
 SET @ErrorMsg = ERROR_MESSAGE()
 END CATCH

 EXEC @Result = sp_OADestroy @Obj

IF (@ErrorMsg IS NOT NULL) OR (@HTTPStatus <> 200) BEGIN
 SET @ErrorMsg = 'Error in spGeocode: ' + ISNULL(@ErrorMsg, 'HTTP result is: ' + CAST(@HTTPStatus AS varchar(10)))
 RAISERROR(@ErrorMsg, 16, 1, @HTTPStatus)
 RETURN 
 END

SET @XML = CAST(@Response AS XML)
SET @Distance = @XML.value('(/DirectionsResponse/route/leg/distance/text) [1]', 'varchar(40)') 

 SELECT  @Distance AS Distance ,@XML AS XMLResults

Select allOpen in new window