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)
)