Home:Learn XQuery:XQuery Examples:MySQL Examples:Archiving XML in MySQL with XQuery

Archiving XML in MySQL^® using DataDirect XQuery^®

In Processing XML using DataDirect XQuery^® and Processing EDI as XML using DataDirect XQuery^®, 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 DataDirect XQuery^® to archive information about the incoming orders in the same MySQL database?

DataDirect XQuery^® 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:

    declare variable $canonicalOrder
        as document-node(element(*, xs:untyped)) $canonicalOrder external
    for $book in $canonicalOrder/order/book
    return
        ddtek:sql-insert("orders", "isbn", $book/ISBN, "quantity", $book/quantity)

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 DataDirect XQuery^®. Note that DataDirect XQuery^® defaults to “auto-commit” mode, but that behavior can be changed programmatically as described in the DataDirect XQuery^® User's Guide and Reference documentation.

What Next?

Now that you have some understanding of how flexible and easy to use DataDirect XQuery^® is for working with XML and non-XML formatted data like EDI, and how it can be used to programmatically update relational databases like MySQL, why not try it yourself for free today?