Home Services Partners Company
Shredding XML documents into tables, a database independent approach...

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

May 25, 2008 0 Comments

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

 

[cc lang="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)[/cc]

 

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

 

[cc lang="xquery"] 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)[/cc]

 

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.

 

[cc lang="xquery"]

[/cc]

 

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

 

 

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

 

[cc lang="xquery"] 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) )[/cc]

 

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.

Marc Van Cappellen

View all posts from Marc Van Cappellen on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.

Read next SAS ODBC Access to Salesforce, SQL Server and Marketo
Comments
Comments are disabled in preview mode.