Data Integration Suite – Archive XML in MySQL

In Process XML using MySQL and Process 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:

Click to Enlarge

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:

Click to Enlarge

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 Process XML using MySQL. Note that Data Integration Suite defaults to "auto-commit mode, but that behavior can be changed programmatically.