Retrieve data from an external JSON or XML service

Question

I need to regularly extract data from an external system using a web request.

As example, I download and import the Exchange rates from the European Central Bank.

The service URL is 'http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml'

Answer 1

As of dbFront 1.2.0.0730, you can now select the action button type of WebRequest.

This allows you to pull data into a table field. You can receive the data as either RAW, JSON or XML.

• RAW
  You would mark the data as RAW if the data is neither Json or Xml or if you want to process the data yourself using database specific JSON and XML handling functionality.

• JSON
  If you select JSON then you need to specify a JsonPath string that will be used to parse and extract the data you want.

• XML
  If you select XML then you need to specify the XPath string that will be used to parse and extract the data you want.

In the original request the URL pointed to an XML data set. To retrieve the currency type and rate you would:

1. Add a new Action Button of the type WebRequest,
2. Set the URL to: 'http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml'
3. Set the data type to XML,
4. Set the XPath string to: "//Cube/@rate|//Cube/@currency".

The result would be a listing of Currency Names and Currency Rates.

JsonPath Syntax and Help

• goessner.net / JsonPath : JsonPath Documentation
• jsonpath.com/ : JsonPath Tester

XPath Syntax And Help

• w3schools.com / xpath_syntax : XPath Documentation.
• xpather.com: an XPath tester.