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
 

PURCHASE STYLUS STUDIO ONLINE TODAY!!

Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!

Buy Stylus Studio Now

Try Stylus Powerful XQuery IDE

Download a free trial of our award-winning IDE for XQuery today!

Learn XQuery in 10 Minutes!

Say goodbye to 10-minute abs, and say Hello to "Learn XQuery in Ten Minutes!", the world's fastest and easiest XQuery primer, now available for free!

Why Pay More for XML Tools?

With Stylus Studio® X16 XML Enterprise Suite, you get the most comprehensive XML tool suite at one incredibly low price. Value: it's just one of many reasons why smart XML developers are choosing Stylus Studio!

Top Ten XQuery Trends

Read about the top 10 XQuery Trends and how they will impact change the way enterprise software applications are built.

 
Free Stylus Studio XML Training:
W3C Member