Shredding XML documents into tables, a database independent approach...

We see more and more DataDirect XQuery users shredding XML documents into a relational database. Reading specific data out of XML documents and storing it in their database. Any relational database, Oracle, SQL Server, DB2, Informix, Sybase, MySQL, PostgreSQL,... you name it!

Ok, I see some of you already asking... "Why not using a native XML database to store the complete XML documents?" There is not a single answer to the question, as always it depends on many factors. Are your processing data-centric XML? Do you need to store the data in an existing relational database? Should the data in your database be queryable through reporting tools? Are you enhancing an existing application with an XML interface? Etc, etc. If you answer positive to one or more of these questions, it is worth considering an approach where the XML documents are shredded into relation tables.

Fine, but why DataDirect XQuery? Most RDBMS solutions offer already the ability to shred XML documents in a relational database. Like explained in this IBM developerWorks paper, Shred XML documents using DB2 pureXML.

There are a variety of reasons... These database solutions are mostly vendor specific. And even if your organization deploys only one database brand, you are likely to run into serious incompatibilities over different versions of the database. These solutions are mostly cumbersome in usage. What about scalability when it comes to processing large document, in the hundreds of megabytes or several gigabytes? What if specific data transformations are required? Etc, etc.

DataDirect XQuery answers most of these concerns. Bulk load of XML data into a relational database includes some simple but illustrative examples. Like in the next query where books are uploaded in the shipments table. Note that in addition to the idea of bulk load, there is also the need to transform and validate some of the data. All fairly simple using XQuery.

 declare variable $shipment
   as document-node(element(*, xs:untyped)) external;

 for $book in $shipment/order/book
 return
   ddtek:sql-insert("shipments",
     "DATE", current-dateTime(),
     "ISBN", $book/isbn,
     "TITLE", upper-case($book/title),
     "QUANTITY",
       if ($book/@quantity) then $book/@quantity else 1)

Or to make the example a bit more complex, consider the following database independent upsert scenario.

 declare variable $shipment
   as document-node(element(*, xs:untyped)) external;

 for $book in $shipment/order/book
 let $quantity := if ($book/@quantity) then
                    xs:integer($book/@quantity)
                  else
                    1
 let $shipment :=
   collection("shipments")/shipments[ISBM = $book/isbn]
 return
   if($shipment) then
     ddtek:sql-update($shipment,
       "QUANTITY", $shipment/QUANTITY + $quantity) 
   else
     ddtek:sql-insert("shipments",
       "DATE", current-dateTime(),
       "ISBN", $book/isbn,
       "TITLE", upper-case($book/title),
       "QUANTITY", $quantity)

But there is of course much more you can do. Another example, inspired by the developerWorks article mentioned above, consider a so called "bill of materials" XML document.

<items>
  <item desc="computersystem" model="L1234123">
    <part desc="computer" partnum="5423452345">
      <part desc="motherboard" partnum="5423452345">
        <part desc="CPU" partnum="6109486697">
          <part desc="register" partnum="6109486697"/>
        </part>
        <part desc="memory" partnum="545454232">
          <part desc="transistor" partnum="6109486697"/>
        </part>
      </part>
      <part desc="diskdrive" partnum="6345634563456">
        <part desc="spindlemotor" partnum="191986123"/>
      </part>
      <part desc="powersupply" partnum="098765343">
        <part desc="powercord" partnum="191986123"/>
      </part>
    </part>
    <part desc="monitor" partnum="898234234">
      <part desc="cathoderaytube" partnum="191986123"/>
    </part>
    <part desc="keyboard" partnum="191986123">
      <part desc="keycaps" partnum="191986123"/>
    </part>
    <part desc="mouse" partnum="98798734">
      <part desc="mouseball" partnum="98798734"/>
    </part>
  </item>
</items>

This data with recursive part elements,can be represented in a relational table.

Nothing more than the following simple query will get you there.

 for $item in /items/item
 return
   (
   ddtek:sql-insert("itemtest",
                    "itemname", $item/@desc,
                    "id", $item/@model)
   ,
   for $part in $item//part
   return
     ddtek:sql-insert("itemtest",
                      "itemname", $item/@desc,
                      "parent", $part/../@desc, 
                      "description", $part/@desc,
                      "id", $part/@partnum)
   )

We've shown how easy you can shred XML documents and load data into relation tables. And important, all this in a scalable and database independent way.

Bulk load of XML data into a relational database

Recently I received a question about the following topic:
We are trying to come up with a solution to bulk insert XML data into a flat
structure table in an Oracle database. Our questions are:
1. Is the performance favorable using DataDirect XQuery?
2. Do you have any sample code we can use for a proof of concept?

Those are two good questions that are becoming more and more popular among our users. And DataDirect XQuery has very good answers for them! What about performance? Well, performance always depends on many factors, of course; how is the data structured in the XML document(s), for example; how does it need to be shred into columns, maybe into multiple tables; what kind of "massage" data needs to go through, and so on. But in general, users can rely on the DataDirect XQuery streaming and projection capabilities to boost scalability and on its ability to work efficiently with relational data sources to leverage performance.

What about some example? Consider the following hypothetical scenario: your warehouse receives daily shipments of books; details about the books are available in XML documents structured like this:
 <shipment>
  <book bookid="1" quantity="15">
   <title>Java Message Service</title>
   <isbn>0596000685</isbn>
   <subject>Java Message Service (JMS)</subject>
  </book>
  <book bookid="2" quantity="25">
   <title>Instant UNIX</title>
   <isbn>1874416656</isbn>
   <subject>GNU/Linux</subject>
  </book>
 </shipment>

Real shipment lists are actually made of hundreds of thousands of entries.
The SQL database used by your warehouse has a "shipments" table which is defined like this:
 CREATE TABLE [dbo].[shipments](
   [DATE] [datetime] NOT NULL,
   [ISBN] [nchar](10) NOT NULL,
   [QUANTITY] [decimal](18, 0) NOT NULL,
   [TITLE] [nvarchar](max) NOT NULL
 ) ON [PRIMARY]

You can write an XQuery loading all the data in the XML document into the "shipments" table quite easily, relying on the DataDirect XQuery RDBMS update capabilities:
 declare variable $shipment
     as document-node(element(*, xs:untyped)) external;
 for $book in $shipment/order/book
 return
  ddtek:sql-insert("shipments",
    "DATE", current-dateTime(),
    "ISBN", $book/isbn,
    "TITLE", $book/title,
    "QUANTITY", $book/@quantity)

Of course there may be cases in which the data been inserted in the database needs to be somewhat modified; for example, support the "TITLE" columns requires all capital case letters:
 for $book in $shipment/order/book
 return
  ddtek:sql-insert("shipments",
    "DATE", current-dateTime(),
    "ISBN", $book/isbn,
    "TITLE", upper-case($book/title),
    "QUANTITY", $book/@quantity)

...or maybe something a little bit more interesting: suppose the @quantity attribute in the input XML document is optional, and if it's missing it means quantity is actually 1:
 for $book in $shipment/order/book
 return
  ddtek:sql-insert("shipments",
    "DATE", current-dateTime(),
    "ISBN", $book/isbn,
    "TITLE", upper-case($book/title),
    "QUANTITY",
      if ($book/@quantity) then $book/@quantity else 1)

Or maybe the single XML needs to be shred in multiple tables, for example doing something like this:
 let $now := current-dateTime()
 for $book in $shipment/order/book
 return (
  ddtek:sql-insert("shipments1",
    "DATE", $now,
    "ISBN", $book/isbn,
    "TITLE", $book/title),
  ddtek:sql-insert("shipments2",
    "DATE", $now,
    "ISBN", $book/isbn,
    "QUANTITY", $book/@quantity)
 )

DataDirect XQuery is able to run these XQueries in a streaming fashion; so, users will be able to upload millions of records without scalability issues. Yet another way in which DataDirect XQuery helps you bridge the XML and SQL worlds in a highly scalable and performant way.