Storage of Structured XML Documents ("Generated XML")

Data may be in the form of structured documents, where the structure is known a priori and is the same for all instances. In this case, the document can be stored in relational or object-relational structures. In this case as well, the object-relational type system can provide a direct mapping to the XML document.

This mapping is relatively straight forward and Oracle XML-SQL Utility (XSU) offers an insert mechanism that can map an XML document directly into a given table or view.

XML-SQL Utility Stores XML Data By Preserving XML Structure

To reiterate, XML-SQL Utility stores XML data by preserving XML structure as follows:

• Tag names are mapped to columns

• Text-only data (elements) are mapped to scalar columns

• Sub-elements are mapped to object types

• Element lists are mapped to collections...

XSU Example: Inserting XML Document into Database

The example XML document given earlier can be inserted into the database using the XML-SQL Utility as follows:

String xmlDoc = " ...the actual xml document... ";
Connection conn = DriverManager.getConnection(...);
OracleXMLSave sav = new OracleXMLSave(conn,"purchaseOrderTab");
sav.insertXML(xmlDoc);

XML Document Object-Relational Storage: Advantages

The advantage of storing an XML document as an object-relational instance is that the structure of the document is preserved in the database as well. This allows the XML document to be viewed and traversed in SQL in a way similar to a XPath traversal on the document.

For instance a XPath traversal such as,

//purchase_order[pono=101]/shipaddr/street

can be easily represented as an attribute traversal in SQL:

SELECT   po.shipaddr.street 
  FROM   purchase_order_tab po 
  WHERE  po.pono = 100;

Mapping to Object-Relational storage enables existing database applications to work against XML data. Also, functionality provided by Oracle8i on Object-Relational columns, such as indexing, partitioning, and parallel query, can be leveraged.

XML Document Object-Relational Storage: Disadvantages

However, using such a mapping, the original document is not exactly reproducible. For instance, comments are lost. But this can be avoided by storing a copy of the original document in a CLOB as discussed in the following section, and using the object-relational mapped data for query efficiency purposes.

Another potential problem could arise due to the ordering amongst the elements. In order to preserve the element ordering, you can have a special column in the underlying table and order the results using that column.