TSQL XML Parsing

Question

I'm calling a webservice (url below) and am getting the XML results back within SQL Server as a varchar(8000) and then converting that to XML. This works perfectly. I want to parse this XML information out into it's individual values but continue to get null values. This is my first attempt in using XML on my SQL 2008 server so I know I'm missing a very trivial item.

http://dev.virtualearth.net/Services/v1/GeocodeService/GeocodeService.asmx/Geocode?culture=en-us&count=10&query=1%20microsoft%20way,%20redmond,%20wa&landmark=&addressLine=&locality=&postalTown=&adminDistrict=&district=&postalCode=&countryRegion=&mapBounds=&currentLocation=&curLocAccuracy=&entityTypes=&rankBy=

I'm taking the response received and storing it into @XML.

SET @XML = CAST(@Response AS XML)

I'm next trying to pull out the Postal Code to get my results and receive a NULL or the wrong node.

Returns NULL

SELECT @XML.value('(/GeocodingResult/Results/Address/PostalCode) [1]', 'varchar(50)') 

Returns "Copyright © 2010 Microsoft and its suppliers. All " (without the quotes)

SELECT @XML.value('(/) [1]', 'varchar(50)') 

Answer 1

Your XPath is wrong - your root node is GeocodingResponse (not GeocodingResult), and you're missing a GeocodingResult along the way.

Try this XPath:

/GeocodingResponse/Results/GeocodingResult/Address/PostalCode

or this SQL XQuery:

SELECT 
   @XML.value('(/GeocodingResponse/Results/GeocodingResult/Address/PostalCode) [1]', 
              'varchar(50)')