Struggling with trying to get XML into a database? Recently DataDirect was asked: What is the easiest solution to bulk insert XML data into a flat
structure table in an Oracle database? and ...
- Is the performance favorable using DataDirect XQuery?
- Do you have any sample code we can use for a proof of concept?
The need for saving XML files to a database is quite common since XML data can be found everywhere;
and DataDirect XQuery has an easy and highly scalable solution for doing just that.
Scalability and performance always depends on many factors, for example; 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 work efficiently with relational data sources to leverage performance.
In this tutorial we'll show you several examples of how to store XML file in database tables.
Storing XML Files in a Database
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 perhaps 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)
)
Storing XML files in Databases, in a Nutshell
DataDirect XQuery is able to run these XQueries that save XML into a database 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. If you need to import, load or save XML into a database, download a free trial today!.