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

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 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


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!

Attend a Live Webinar This Week!

Learn about Stylus Studio's unique features and benefits in just under an hour. Register for the Stylus Studio QuickStart Training WebCast!

Ask Someone You Know

Does your company use Stylus Studio? Do your competitors? Engineers from over 100,000 leading companies use Stylus Studio, and now you can ask someone from your own organization about their experiences using 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