XML in the Database: Generated and Authored XML

Oracle8i supports different aspects to using XML in the database.

• "Generated XML": Using XML as an Interchange Format. The most common way of using XML in the database is to use XML as a interchange format where existing business data is wrapped in XML structures. In this case the XML format is used only for the interchange process itself and is transient.

• "Authored XML": Store and query XML documents in the database.

Generated XML

XML can be generated from object-relational tables and views. The benefits of using object-relational tables and views as opposed to pure relational structures are discussed below. Oracle provides a free utility, XML-SQL Utility (XSU) available for download from the Oracle Technology Network (OTN.

XML-SQL Utility (XSU) Converts SQL Query Results into XML

This utility converts the result of a SQL query into XML by mapping the query alias or column names into the element tag names and preserving the nesting of object types. The result representation can be in text or a DOM (Document Object Model) tree, the generation of the latter avoids the overhead of parsing the text to directly realize the DOM tree.

Mapping Between XML and the Database

There is a clean relationship between structured XML instances and object-relational types:

• Columns map to top level elements.

• Scalar values map to a elements with text only content.

• Object types are mapped to elements with its attributes appearing as sub-elements. Collections map to lists of elements.

• Object references and referential constraints can be mapped to IDREFs in the XML document.

Database Mapping Example 1 (Java): Generating an XML Instance Corresponding to an SQL Query

For instance, the following Java code generates an XML instance corresponding to an SQL query.

public void testXML()
 {
   DriverManager.registerDriver(
      new oracle.jdbc.driver.OracleDriver());

   //initialize a JDBC connection
   Connection conn = 
        DriverManager.getConnection(
                    "jdbc:oracle:oci8:scott/tiger@");

   //initialize the OracleXMLQuery; 
   OracleXMLQuery qry = 
	         new OracleXMLQuery(conn,
                   "select * from purchaseOrderTab");
                                        
   // set the document name
   qry.setRowsetTag("PurchaseOrderList"); 
   // set the row element name
   qry.setRowTag("PurchaseOrder"); 
    // get the XML result
   String xmlString = qry.getXMLString(); 
    // print result
   System.out.println(" OUPUT IS:\n"+xmlString);
}

XML Example 2: XML Document Produced from Generic Mapping

The query in Example 4 selects all top level elements from the purchase order table and we use the generic mapping to get the following XML document:

<?xml version='1.0'?>
<PurchaseOrderList>
   <PurchaseOrder num="1">
     <purchaseNo>1001</purchaseNo>
     <purchaseDate>10-Jan-1999</purchaseDate>
     <customer>
        <custNo>100</custNo>
        <custName>Hose</custName>
        <custAddr>
           <street>200 Redwood Shrs</street>
           <city>Redwood City</city>
           <state>CA</state>
           <zip>94065</zip>
        </custAddr>
     </customer>     
     <lineItemList>
        <lineItem>
           <lineItemNo>901</lineItemNo>
           <lineItemName>Chair</lineItemName>
           <lineItemPrice>234.55</lineItemPrice>
           <lineItemQuan>10</lineItemQuan>
        </lineItem>
        <lineItem>
           <lineItemNo>991</lineItemNo>
           <lineItemName>Desk</lineItemName>
           <lineItemPrice>3456.63</lineItemPrice>
           <lineItemQuan>20</lineItemQuan>
        </lineItem>
     </lineItemList>
   </PurchaseOrder>
   <PurchaseOrder>
      <!-- more purchase orders. -->
   </PurchaseOrder>
</PurchaseOrderList>

The XML document created is an exact structural replica of the object type. Using object views, you can create such object-relational mappings from existing relational tables.