Using XQuery To Generate JSON from Relational Data

This article describes how to build a simple multi-tier solution to expose relational data to a modern HTML application using JSON.

Assume you have a database which captures information on actors and movies; one is freely available at http://dev.mysql.com/doc/sakila/en/index.html.

We want to expose the database through a simple REST web service interface which allows a variety of client technologies to query the data without being tightly coupled to the underlying database product.

We can write a simple XQuery, taking advantage of DataDirect XQuery which supports all major relational databases including, MySQL, SQL Server, Oracle, IBM DB2 and Sybase.

Using the Stylus Studio XQuery mapping tool takes just a few seconds to create a query which returns an XML document from a database View. It only takes 4 steps using Stylus Studio:

• Drag & drop the DB view to the source panel
• Set the target schema which will provide the XML shape we want to generate in output
• Connect the repeating element film _list to the target element movies
• Use Auto-Link and let Stylus Studio figure out the rest

We can quickly run the query to verify that everything works as expected.

Returning all records does not sound like a very useful operation. We can make our query more useful by adding two parameters to filter the results. We want to be able to filter by category and rating.

Once we have defined the parameters, the scenario dialog allows for binding the values to them in order to test the query.

If we run our query again we can see that the result contains only movies which are Documentary and have a PG rating.

DataDirect XQuery ships with a servlet implementation (see chapter 9 in the DataDirect XQuery User’s Guide http://media.datadirect.com/download/docs/ddxquery/allddxq/reference/webserviceframework2.html#wp201878) which can turn any query, including the one we have been working on, into a REST web service. The following screenshot shows how to invoke our query using just a web browser.

We are almost there. Now we need to turn our query result into JSON. DataDirect XML Converters features a large variety of converters including XML to/from JSON.

DataDirect XQuery can easily pipe the result into any XML Converter, we just need to add one line to the query prolog to turn our XML output document into JSON.

declare option ddtek:serialize "method=JSON";

Now you have it, a simple REST service which queries a relation database and returns JSON data which can be consumed by any internet browser with JavaScript support.

We hope you enjoyed reading this tutorial. If you have any questions or feedback on this tutorial do not hesitate to contact us.

- Stylus Studio Team

 Technical Support

 Follow us on Twitter

 Connect on Facebook