In this blog, we are going to see how to call Google maps API and parse the returned XML document via SQ using httpgetclob , Xmltable, Xmlparse functions

Sample SQL :

Select * From

Xmltable(‘$d/GeocodeResponse/result/geometry/location’

passing

Xmlparse(Document systools.httpgetclob(‘http://maps.googleapis/maps/api/geocode/xml?address=East+Evergreen+DR%2C+Palatine%2C+IL’, ”)) as “d”

Columns Latitude Varchar(128) path ‘lat’,

Longitude Varchar(128) path ‘lng’)

as Webserviceresult ;

Xmltable function extracts XML data in the form of a relational table

Xmlparse is a function that parses the argument as an XML document and returns an XML value.