Get Google lat/long via SQL from IBM i

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(‘//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

IBM i talks to WATSON

We can invoke Watson API from IBM i using DB2’s SQL function Httpgetclob. Httpgetclob retrieves a text-based resource from the specified URL through an Http Get Request.Httpgetclob returns the resource as CLOB(5M) data. Example : Calling WATSON language translate Api to convert english to french using httpgetclob. Select Char(Systools.httpgetclob(‘//watson-api-explorer.mybluemix.net/language-translator/api/v2/translate?model_id=en-fr&text=good+night’, ‘ ‘), 256) From Sysibm/Sysdummy1 Result

Sql TRANSLATE function

The Sql TRANSLATE() function replaces a sequence of characters in a string with another sequence of characters. The function replaces a single character at a time. Example: Select TRANSLATE(‘This@is@an@example.’,  ‘ ‘,  ‘@’ ) From Sysibm/Sysdummy1 Result:    This is an example.