Home Services Partners Company
Bulk load of XML data into a relational database

Bulk load of XML data into a relational database

by Minollo
January 04, 2008 0 Comments

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

0596000685 Java Message Service (JMS)

1874416656 GNU/Linux

[/cc] 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: [cc lang="sql" theme="xquery"]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] [/cc] 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: [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", $book/title, "QUANTITY", $book/@quantity) [/cc] 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: [cc lang="xquery"]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) [/cc] ...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: [cc lang="xquery"]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 maybe the single XML needs to be shred in multiple tables, for example doing something like this:

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

Minollo

View all posts from Minollo 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.