Search code examples
sql-serverxquery-sql

SQL Query + Google Geocode Response XML

I have the XML response from the Google Geocoding API stored in a SQL Server XML column. Can someone help me with a query that will return all rows where the XML contains > 1 result?

e.g.

<GeocodeResponse>
  <status>OK</status>
  <result></result> <!-- more than one result is present -->
  <result></result>
  <result></result>
</GeocodeResponse>

So something like this gives me the first result:

SELECT XmlResponse.query('/GeocodeResponse/result') FROM Locations

But I'm not sure where to go from here...

Solution

  • You can use the exist() method of the XML data type to check if there exist a second <result> node.

    select *
from Locations
where XmlResponse.exist('GeocodeResponse/result[2]') = 1

    Test the query here. https://data.stackexchange.com/stackoverflow/q/101340/xmlcolumn-exist