MySQL Examples Tutorial

Users with MySQL database deployments (Enterprise version) can now take advantage of all the benefits of DataDirect's XQuery engine. DataDirect XQuery provides high-performance, scalable, reliable XQuery for MySQL users. Additionally, DataDirect XQuery provides integration capability with MySQL, other relational databases, and EDI.

Examples

The following examples show you how you can use DataDirect XQuery to work with data in a MySQL relational database. Consider the following scenario: Your company receives book orders from a variety of partner companies; when an order is received, an XML document consistent with a "canonicalq internal XML Schema (structure) must be created so that it can be sent to the warehouse to fulfill the order. Orders are usually submitted in a proprietary XML format; but in some cases orders are not even in XML format; nor is the information contained in these orders a subset of what the "canonical" internal format requires. Once fulfilled, orders also need to be archived in the company’s main database for archiving/reporting purposes. Your company relies on a MySQL database to store information about all the books in inventory, and to keep track of the orders.

As you'll see, XQuery, and in particular the DataDirect XQuery implementation, provide a great way to solve this and similar problems.

A partner company XYZ sends you book orders using their proprietary format:

Our company's canonical XML format relies on the following structure, and it exposes the following information:

While the basic format of the order XML and canonical XML is similar, there are a few differences that we'll need to address before the order can be processed. While the structural hierarchy of the XML is the same (both have order, book, and ISBN elements) the order XML uses lowercase for the ISBN element name, and it includes quantity as an attribute of the book element. Further, the canonical XML includes several other elements not present in the order XML, like those for title, publisher, and publishing date.

The following XQuery takes care of the simple format changes we need to implement. Note that we have decided to reference the incoming order as an external variable so that we can bind the right content dynamically as part of our application:

Now that the formatting discrepancies have been addressed, how do we add the missing information about title, publisher and publishing date?

Our company relies on MySQL database to keep detailed information about all the books available in inventory. In particular, the "books_dbo" schemata (in MySQL terms) contains a "booksxml" table that contains all the details about the books in inventory that we need. Let's look at this table in the MySQL Query Browser:

Data Integration Suite is able to access MySQL schemata and tables. And it’s able to do that in a performant and scalable way, as described in more details in a DataDirect white paper – The Data Integration Suite Performance: Generating SQL. For example, you can access data from the “booksxml” table for a specific ISBN number this way:

In this case, the result of this XQuery is:

This result is the XML representation of one specific row in the MySQL "booksxml" database table. If you are curious about how Data Integration Suite is retrieving that result, take a look at the query plan that Data Integration Suite is actually executing:

As you can see, the selection of the matching ISBN number is entirely pushed as SQL query to the database; the Data Integration Suite engine “only” does the job of rendering the returned information as XML.

In our case, however, we want to do something a bit more complicated: We want to join data available in the incoming XML order with data available in the MySQL database, and we want to return an XML document consistent with our canonical format. Merging together the two XQueries described above, we can accomplish this quite easily:

A single XQuery allows us to process an incoming order which uses a proprietary XML format, to augment the information available in that format with data stored in our MySQL database, and to return a result consistent with our desired XML canonical format.

Executing XQuery Programmatically

Data Integration Suite implements the XQJ makes it easy for XQuery execution to become part of your Java application, or part of services exposed by your application server. If you are familiar with JDBC, you will notice many similarities between XQJ and JDBC. Take a look at the following code snip from a Java application:

In a "real life" scenario, the result of the XQuery would probably be consumed differently, either saving it to a file/stream, consuming it as SAX or StAX events, or maybe materializing it as an in-memory XML representation (DOM, the least scalable of the options).

Now we know how to merge data available in XML documents and MySQL, and how to format a result consistent with a specific XML Schema. But what if a partner company is sending us order information in something other than XML?

In the previous topic, Processing XML using MySQL, you learned how you can merge data available in XML documents and MySQL, and how to format a result consistent with a specific XML Schema. But some of our partner companies don't use XML at all; they submit book orders in EDI format, specifically using one of the EDI messages part of the EANCOM family. Here's what an incoming order message looks like in EDI:

Of course, we would like to handle EDI as easily as we handled the proprietary XML sent to us by other partners. Luckily enough, Data Integration Suite is able to provide access to a wide variety of EDI messages as if they were XML messages. For Data Integration Suite, EDI messages are just yet another XML format for us to handle. For example, the EDI message above will look like this XML fragment from the Data Integration Suite point of view:

Converting this format in something similar to our canonical XML format is simple using Data Integration Suite:

As in the previous example, Processing XML using MySQL, we are still missing information about title, publisher, and publishing date. We can add that information retrieving it from our MySQL database, where the 'booksxml' table contains details about our books in inventory:

This XQuery will return a result consistent with the same canonical XML format described in the previous example. As you can see, this single XQuery is able to merge EDI data with information stored in our MySQL database, and format the result to be consistent with a specific XML Schema.

The last step we need to accomplish is to archive order information in our MySQL database for reporting purposes. Can we do that using Data Integration Suite?

Next: MySQL Example – Archiving XML in MySQL

In Processing EDI as XML using MySQL, we saw how to merge XML and EDI incoming orders to become consistent with our XML canonical format, and how we can use information stored in our MySQL database to augment data available in the incoming orders themselves. But how can we leverage Data Integration Suite to archive information about the incoming orders in the same MySQL database?

Data Integration Suite supports the ability to store data into MySQL databases. In our case we want to store information in an "orders" table inside the "books_dbo" MySQL schemata:

The "orders" table is initially empty (as you can see from the picture), and it contains only two columns: "isbn" and "quantity." The following XQuery processes an XML canonical format created in one of the previous examples and stores information about the order in the MySQL database:

After running this XQuery, the "orders" table in our MySQL database is populated with the information contained in our order:

This XQuery can easily become part of the application that processes incoming book orders, getting executed after the book order has been transformed in our XML canonical format. The Java code required to execute this XQuery is virtually identical to what is described in Processing XML using MySQL. Note that Data Integration Suite defaults to "auto-commit mode, but that behavior can be changed programmatically.