Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
|
Introduction to Oracle XML, 11 of 27
Oracle8i supports different aspects to using XML in the database.
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.
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.
There is a clean relationship between structured XML instances and object-relational types:
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); }
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.
|
Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|