2

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

Dave Ziegler
  • 1,737
  • 3
  • 19
  • 36

1 Answers1

2

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

Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281