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 (http://maps.googleapis.com/maps/api/directions/xml?origin=Boston,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 = '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

Open in new window

crundleAsked:
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.

AmmarRCommented:
dear

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

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

varchar(max)

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

2crundle

...
set @URL = 'http://maps.googleapis.com/maps/api/directions/xml?origin=Boston,MA&destination=Charleston,sc&sensor=false'

 DECLARE @Response varchar(8000)  <--- varchar(max) ???
 DECLARE @XML xml
....
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.
AmmarRCommented:
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
-----------
http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/10/24/sql-server-shows-invalid-data-when-reading-large-xml-file-using-extended-stored-procedures-sp-oacreate.aspx
------------------------
Memory and Handle Leak When You Use MSXML Inside SQL Server Stored Procedure
http://support.microsoft.com/default.aspx?scid=kb;EN-US;303114

----------------
http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-whats-new-xml.aspx

Microsoft dont recommend your method

Regards
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?
AmmarRCommented:
why dont you use vb or  VB.net 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.

http://msdn.microsoft.com/en-us/library/ms766431(VS.85).aspx
http://forums.asp.net/p/1540142/3749875.aspx
http://www.velocityreviews.com/forums/t297662-using-webrequest-to-replace-msxml3-dll-com-object.html

or you can try different methods to get webpage content in vb
http://www.mikesdotnetting.com/Article/49/How-to-read-a-remote-web-page-with-ASP.NET-2.0
http://msdn.microsoft.com/en-us/library/system.net.webclient(VS.80).aspx

--

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.