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
if ($book/@quantity) then $book/@quantity else 1)[/cc]
Or to make the example a bit more complex, consider the following database independent upsertscenario.
for $book in $shipment/order/book
let $quantity := if ($book/@quantity) then
let $shipment :=
collection("shipments")/shipments[ISBM = $book/isbn]
"QUANTITY", $shipment/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.
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
for $part in $item//part
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.
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.
Subscribe to get all the news, info and tutorials you need to build better business apps and sites
Copyright © 2019 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.
Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.