How To : Load XML data into multiple tables

Here’s a short article presenting a common technique to load data from a single XML document into different tables, using a single INSERT statement.
A typical use case is an XML structure describing a master-detail relationship, such as the classic invoice or purchase order document (header + line items).

How To : Read and Write Office 2003 Excel XML Files

Office Open XML (OOX) has become the default format with the release of Office 2007, but back in the 2003′s days, Microsoft had already developed a format to store Excel workbooks as XML.
A comprehensive overview is available here :
Dive into SpreadsheetML (Part 1 of 2)
Dive into SpreadsheetML (Part 2 of 2)

Contrary to OOX where data and metadata are stored in a multipart archive, an Excel workbook file in SpreadsheetML 2003 format consists in a single XML instance, and therefore easily managed using built-in Oracle XML functions and XML DB features.
In this article, I’ll focus on how to create and read such files with the help of SQL/XML functions, XSLT and XQuery.

XML DB Events : Reading an Open Office XML document (.xlsx)

This post is actually an offshoot of a recent article by Marco Gralike on his blog. Marco describes how to automatically read an Open Office XML (OOX) document, specifically docx format, and store the data in a database table for subsequent queries.
One of the key features is the definition of XML DB Events handlers that will “unzip” the archive for us whenever we drop it in the repository.

The funny thing is that, in the meantime, I had already begun to work on the xlsx version, including my own unzip utility. Marco’s example is using Anton Scheffer’s great zip/unzip package which, although based on the same extraction technique, is much more complete than mine.

Installation script and package for my demo are available here : oox_sml.zip
I’ll explain some of the steps below in detail.

How To : Flatten out an XML Hierarchical Structure

Besides the usual requirement to present XML data in a relational way, sometimes we may also need to flatten the whole XML structure and build parent-child relationships from it.
For instance, converting this :

<root>
 <item>ABC<item>
 <item/>
 <item>
   <subitem>XYZ</subitem>
 </item>
<root>

to

    TAG_ID TAG_NAME        TAG_VALUE          PARENT_TAG_ID
---------- --------------- ---------------- ---------------
         1 root
         2 item            ABC                            1
         3 item                                           1
         4 item                                           1
         5 subitem         XYZ                            4

Internal handling of XQuery functions

… or more precisely user-defined recursive XQuery functions.
I recently stumble upon this while checking the explain plan of a query :

How To : Sort delimited values in a string using XQuery

This follows a thread on OTN about a month ago. The question was : how to sort a string in ascending order?
I suggested an XQuery approach that turned out to be a lot more efficient than the traditional TABLE(SUBQUERY-FETCH) pattern.

How To : Generate a recursive XML structure

Here are three techniques to generate a recursive XML structure, e.g. :

<employee id="100" name="King">
  <employee id="101" name="Kochhar">
    <employee id="108" name="Greenberg">
      <employee id="109" name="Faviet"/>
      <employee id="110" name="Chen"/>
      <employee id="111" name="Sciarra"/>
      <employee id="112" name="Urman"/>
      <employee id="113" name="Popp"/>
    </employee>
    <employee id="200" name="Whalen"/>
    <employee id="203" name="Mavris"/>
    <employee id="204" name="Baer"/>
    <employee id="205" name="Higgins">
      <employee id="206" name="Gietz"/>
    </employee>
  </employee>

  ...

  <employee id="201" name="Hartstein">
    <employee id="202" name="Fay"/>
  </employee>
</employee>