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

How can you get a single value for an XML response that is over 8000 char. Below is the cose and here is the XML (,MA&destination=charleston,sc&sensor=false)

If you change the destination to Boston,MA is works

DECLARE @URL varchar(MAX), @Distance varchar(40)
--Google V3--
 set @URL = ',MA&destination=Charleston,sc&sensor=false'

 DECLARE @Response varchar(8000)
 DECLARE @Obj int 
 DECLARE @Result int 
 DECLARE @HTTPStatus int 
 DECLARE @ErrorMsg varchar(MAX)

EXEC @Result = sp_OACreate 'MSXML2.ServerXMLHttp', @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
 --print @Result

 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)

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

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


varchar can hold maximum of 8000 bytes, so it wont fit your requirnment

try using nvarchar(max)
or ntext
crundleAuthor Commented:
Is it possible to just get the distance?
What is the Max for ntext as the XML is 45000 char?


max indicates that the maximum storage size is 2^31-1 bytes


set @URL = ',MA&destination=Charleston,sc&sensor=false'

 DECLARE @Response varchar(8000)  <--- varchar(max) ???
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

crundleAuthor Commented:
varchar(max) fails and no results, The text, and ntext, are invalid for local variables.
Dear crundle:

i took in your code and tested it and yeah i get your point , its because the xml page is returning a large string value

thats why varchar(8000) doesnt allow it and varchar(max) as well

i read few articles on the net and i think you need to see this
Memory and Handle Leak When You Use MSXML Inside SQL Server Stored Procedure;EN-US;303114


Microsoft dont recommend your method

crundleAuthor Commented:
ok, but I don't see where any instruction to oversome the problem at hand. Do you know of a different method, maybe?
why dont you use vb or to achieve this task, its much more easier and the string datatype in vb can hold that large string returned from the page you are calling.

or you can try different methods to get webpage content in vb


after getting the xml content you can store it in a sql table directly and then since you are using sql 2008, you can use the
new FOR XML with XPath query syntax support, XML data type
or Xquery syntax enhancements

just other options

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crundleAuthor Commented:
good direction
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.