6.4 Using XSU for Fine-Grained Storage

   

---

Handling XML documents in a fine-grained manner is supported by Oracle's XSU (XML SQL Utility). XSU is a programmatic interface for Java and PL/SQL programs. XSU supports two directions:

1. XSU transforms data from object-relational tables or views into XML; XML documents are generated out of relational data.

2. Splitting XML documents into pieces and storing them in tables the other way around.

6.4.1 Canonical Mapping

The basis for both directions is a schematic mapping from relational structures to XML. Given an SQL query related to any tables or views, an XML document is produced:

• The whole query result is enclosed by <ROWSET> . . . <s/ROWSET>

• Each tuple of the result is put in <ROW> . . . </ROW>

• Attribute values turn into tags <Name> . . . </Name> taking the column name

If aliases are used in SQL queries, the aliases are taken as tags instead of column names. Thus, an alias is useful to give expressions such as COUNT(*) a readable name.

These basic mechanisms produce XML documents. This allows for only simple XML documents having a limited nesting level?that is, <ROWSET>, <ROW>, <Attributename>. But the mapping is also able to handle object-oriented concepts in the following way:

• Object types: If an attribute is object-valued, then the object type's attributes become inner tags according to the internal structure.

• Collections: The name of a V Array or a nested table type becomes a tag to enclose the collection.

  The elements of the inner collection use the collection tag and append _ITEM.

• References: A type tag with an object identifier uses the syntax <Tag REFTYPE="REF T"> 0ABD1F6. . . </Tag>.

Furthermore, it is worth mentioning that attributes or aliases starting with an at sign (@) have a special meaning: The values are taken for tag attributes instead of tag values.

The example in Listing 6.11 assumes two tables and three user-defined types for keeping customer information and their phone numbers.

Listing 6.11 Customers and Phone Numbers

CREATE TABLE Customer (CNo INT, FirstName VARCHAR, LastName
                  VARCHAR, Zip VARCHAR, City VARCHAR, Street
                  VARCHAR, Houseno VARCHAR);
CREATE TABLE Phonelist (CNo INT, Phone VARCHAR);
CREATE TYPE Name_Type AS OBJECT (First VARCHAR, Last VARCHAR);
CREATE TYPE Address_Type AS OBJECT (Zip VARCHAR, City VARCHAR,
                                    Street VARCHAR, Houseno VARCHAR);
CREATE TYPE Phone_Tab AS TABLE OF VARCHAR;

The next query (see Listing 6.12) produces a nested structure, constructing objects of type Name_Type with first name and last name, building an Address_Type object, and putting the customer's phones in a Phone_tab.

Listing 6.12 Nested Structure Query

SELECT c.CNo AS @CNo,
       Name_Type(c.FirstName,c.LastName) AS Name,
       Address_Type(c.Zip,c.City,c.Street,c.Houseno) AS Address,
       CAST (MULTISET (SELECT Phone FROM Phonelist WHERE CNo=c.CNo)
                       AS Phone_Tab) AS Phones
FROM Customer c

The query result possesses the following structure:

(Cno int, Name Name_Type, Address Address_Type, Phones Phone_Tab)

whereby Phone_Tab represents an inner table?that is, each value is a list of phone numbers. Each user-defined object type possesses a constructor like Address_Type to create objects; the parameters correspond to the internal type structure. CAST/MULTISET converts a SELECT-FROM-WHERE query into a collection, here of type Phone_Tab. That is, we obtain a "nested result table" having an inner table of phone numbers for each customer.

Processing the query in interactive SQL produces an XML document with a complex nested structure, as shown in Listing 6.13.

Listing 6.13 Query Output

<?xml version="1.0"?>
<ROWSET>
   <ROW NUM="1" CNO="10">
      <NAME>
         <FIRSTNAME> Lucky </FIRSTNAME>
         <LASTNAME> Luke </LASTNAME>
      </NAME>
      <ADDRESS>
         <ZIP> 12345 </ZIP>
         <CITY> Bull </CITY>
         <STREET> Cows Xing </STREET>
         <HOUSENO> 8 </HOUSENO>
      </ADDRESS>
      <PHONES>
         <PHONES_ITEM> 012/3456 </PHONES_ITEM>
         <PHONES_ITEM> 023/4567 </PHONES_ITEM>
      </PHONES>
   </ROW>
   <ROW NUM="2" CNO="20">
   . . .
</ROWSET>

Each tuple is put into <ROW> . . . </ROW> and automatically numbered by an attribute NUM. The attributes of an object type are taken as tags?for example, FIRSTNAME and LASTNAME according to the inner structure of the Name attribute. The XML representation of a nested table uses a PHONES tag for the whole collection, and PHONES_ITEM for each entry.

Since the column CNo is renamed to @CNo, the value is taken as an attribute value CNO="10" in the XML document.

Similarly, an object view can be defined as shown in Listing 6.14.

Listing 6.14 Create a View

CREATE TYPE Customer_Type AS OBJECT
( CNo     INTEGER,
  Name    Name_Type,
  Addr    Address_Type,
  Phones  Phone_List
);
CREATE VIEW MyCustomer OF Customer_Type AS
SELECT . . .  query from above . . . ;

The object type Customer_Type describes the structure of the query that is used to define the object view. Then, SELECT * FROM MyCustomer would return the same XML document. The mapping is then based upon the structure of the object type Customer_Type.

Hence, the approach gains power pretty much from object-relational features and object view mechanisms: Applying object-relational concepts allows for nesting of tags to any depth according to the complex structure. Particularly, the object-relational concepts provide a powerful means to obtain nested XML structures even in case of 1NF tables.

Oracle's XSU defines a Java interface and two packages in the proprietary PL/SQL language to use XSU in programs:

1. OracleXMLQuery and DBMS_XMLQuery, respectively

2. OracleXMLSave and DBMS_XMLSave, respectively

In the following sections, only the handling in Java is presented. The principles of the PL/SQL packages are similar.

6.4.2 Retrieval

Listing 6.15 shows how to use XSU for executing a query in a Java environment and producing XML.

Listing 6.15 XSU Example

Connection conn = //JDBC database connection
   Drivermanager.getConnection("jdbc:oracle:thin:@myhost:1521:mydb",
"scott", "tiger");
OracleXMLQuery q = new OracleXMLQuery(conn,
"SELECT . . . FROM  . . .  WHERE  . . . ");
org.w3c.dom.Document domDoc =q.getXMLDOM();  // DOM representation
XMLDocument xmlDoc = (XMLDocument)domDoc;
StringWriter s = new StringWriter(10000);
xmlDoc.print(new PrintWriter(s));
System.out.println(s.toString());
q.close();

The JDBC database connection specifies the URL of the database and the schema "scott" to be queried. The Java class OracleXMLQuery possesses two constructors that take an SQL query either as a string value or as a JDBC ResultSet:

• OracleXMLQuery(Connection conn, String query)

• OracleXMLQuery(Connection conn, ResultSet rset)

The resulting document can be obtained in different forms by various get methods:

• java.lang.String getXMLString([int metaType])

• java.lang.String getXMLString(org.w3c.dom.Node root [ ,int metaType])

• org.w3c.dom.Document getXMLDOC([ int metaType ])

• org.w3c.dom.Document getXMLDOC(org.w3c.dom.Node root [ ,int metaType])

• void getXMLSAX(org.xml.sax.ContentHandler sax)

getXMLString creates a string representation as output. A variant of getXMLString uses a node-parameter that becomes the root of the resulting document. An optional int-parameter metaType specifies whether a DTD or a XML Schema is to be created for the document. The constants for metaType are DTD, SCHEMA, and NONE (the default).

The getXMLDOC method possesses the same parameters but returns the resulting document in a DOM representation. The code in Listing 6.15 passes the Document object to a PrintWriter for output. Using the DOM representation, the complete functionality of the Oracle XML Development Kit (XDK) can be used then, for example, to parse the document, to perform XSLT transformations, to extract parts of the document, to create a DTD, and so on.

A further method getXMLSAX assigns a SAX-ContentHandler to the document. Hence, the document can be handled in a SAX-conforming manner. The parameter sax must be previously registered.

OracleXMLQuery has various methods for processing results incrementally:

• void setMaxRows(int max): Processes a certain amount of tuples

• void setSkipRows(int n): Skips n tuples (being already read)

• restart(): Executes the query again (for the next increment)

• long getNumRowsProcessed(): Returns the total number of processed tuples

• void keepObjectOpen(boolean): Lets the query (i.e., the underlying ResultSet) remain open for the database session

These methods are useful to convert a query result piecewise into several XML documents: Setting the bulk to n by means of setMaxRows, a loop skips i*n tuples in the i-th run before restarting the query.

Further methods of OracleXMLQuery enable one to rename the predefined tags:

• void setRowTag(java.lang.String tagname): Renames the ROW tag.

• void setRowsetTag(java.lang.String tagname): Renames the ROWSET tag.

• void setRowldAttrName(java.lang.String tagname): Renames the NUM attribute of the ROW tag.

• void setRowIdAttrValue(java.lang.String tagname): Determines an attribute the values of which are taken for the NUM attribute; passing the null object, the rows are sequentially numbered starting with 1.

• void useTypeForCollElemTag(boolean): Tags the entries of the inner collection with the type name of the collection; by default _ITEM is attached to the collection tag.

• void useUpperCaseTagNames(): Puts tag names in uppercase.

• void useLowerCaseTagNames(): Puts tag names in lowercase.

However, the best flexibility is obtained by transforming documents with the help of style sheets. XSL (eXtensible Stylesheet Language) is a powerful language to describe transformations. Particularly, the sublanguage XSLT (XSL Transformations) allows one to transform XML into another text-based format. XSLT is thus ideal to perform additional transformations on the resulting XML document. In XSU, a style sheet is registered as follows by passing a Java String or Reader object:

• void setXSLT(java.lang.String stylesheet)

• void setXSLT(Reader stylesheet)

Additional methods are not explained here in detail. For instance:

• void setStylesheetHeader(java.lang.String uri [, java.lang. String type ]): Sets the style sheet header in the document

• org.w3c.dom.Document getXMLSchema(): Creates an XML schema for the result

• String getXMLMetaData(int metaType, boolean mitVersion): Generates a DTD or an XML schema as a string

6.4.3 Modifications

The other way around, XML documents that conform to the canonical mapping can be stored in a relational table or in a view in a fine-grained manner. Using views, several tables beneath the view definition can be filled. Otherwise, only the storage in one table is possible.

The piece of code in Listing 6.16 shows the principle. Assume the URL of an XML file is given.

Listing 6.16 Using Views

OracleXMLSave sav = new OracleXMLSave(conn, "MyCustomer");
sav.insertXML(sav.getUrl("http://www.myServer.com/myFile.xml"));
sav.close();

The method getURL(String) of class OracleXMLSave is useful for processing files by means of a URL: Given a string that contains a filename or a URL, getURL creates a java.net.URL object, which can be used for insertXML. The content of the file is stored in the view MyCustomer.

The referenced XML document must certainly have a structure that fits to the tables in order to store the derived tuples. One exception to the rule is that tags in the document may be omitted; tags that do not occur are stored as NULL values.

The constructor of OracleXMLSave takes a table or view that is to be filled. Views are useful if XML documents are to be spread across several tables; the view abstracts from the underlying tables, thus defining a mapping that breaks down documents into pieces. The mechanism of view update is used thereby. In general, updating views requires an INSTEAD OF trigger that defines the effect of INSERT, UPDATE, and DELETE on the underlying base tables.

OracleXMLSave possesses three main methods for manipulation:

• int insertXML(org.w3c.dom.Document)

• int updateXML(org.w3c.dom.Document)

• int deleteXML(org.w3c.dom.Document)

The methods are also available with alternative parameters of type java.lang.String, Reader, InputStream, and java.net.URL, each representing the XML document. All the methods return the number of modified tuples.

In case of updateXML and deleteXML, the given document is taken as a pattern for qualifying documents?that is, the document determines the query values of the resulting WHERE condition. Which attributes are really participating in the condition are specified separately by means of the method voidsetKeyColumnList(String[] ). Several attribute names can be passed as a String array. The corresponding tag values are extracted and used in the condition.

The document also defines the values to be changed or stored in case of updateXML and insertXML. That is, the document contains the new values being used for UPDATE . . . SET (updateXML) and INSERT INTO . . . VALUES (insertXML). The attributes to be modified are specified in a method void setUpdateColumnList(String[] ). See Listing 6.17 for an example.

Listing 6.17 insertXML Example

OracleXMLSave sav = new OracleXMLSave(conn, "MyCustomer");
String[] list = new String[2];
list[0] = "CNo";        // insert only CNo and
list[1] = "Name";       // Name in view MyCustomer
sav.setUpdateColumnList(list);
Document doc = sav.getUrl("myUrl");
sav.insertXML(doc);     // extract CNo and Name and insert them

This program performs the following SQL statement:

INSERT INTO MyCustomer (CNo, Name)
VALUES (:cno, :n)

The host variables :cno and :n are the CNo- and Name values extracted from the XML document. An update is shown in Listing 6.18.

Listing 6.18 updateXML Example

list[0] = "Name";             // modify Name and Address
list[1] = "Address";          // in view MyCustomer
sav.setUpdateColumnList(list);
sav.setKeyColumnList("CNo");
sav.updateXML(doc);          // all entries having the
                             //CNo-value are changed

Hence, the effect of updateXML is shown in Listing 6.19.

Listing 6.19 Effect of updateXML

UPDATE MyCustomer
SET Name    = :name,    /* from the document */
    Address = :address  /* from the document */
WHERE CNo = :cno        /* from the document */

Further methods allow for bulk operations and determine the Commit behavior:

• void setBatchSize(int n): Helps reduce client/server communication?n operations are collected and transmitted as a bulk operation to the database server.

• void setCommitBatch(int n): Defines simple transactional behavior?a commit is performed after every n operations.

Again, tags can be renamed by using the set methods such as setRowTag. Furthermore, a style sheet can be registered with setXSLT; it is executed before the document is written to the database. This is another effective manner to affect the storage?that is, use XSLT to transfer an XML document into a suitable form that can then be handled by the canonical mapping.

---

Top Title TOC Preface P.1 What Is XML? P.2 XML Concepts P.3 XML-Related Technologies P.4 XML Data Management P.5 How This Book Is Organized P.6 Who Should Read This Book P.7 Resources Acknowledgments Chapter 8 Chapter 12 Chapter 13 Chapter 14 Chapter 19 Part I: What Is XML? Chapter 1. Information Modeling with XML 1.1 Introduction 1.2 XML as an Information Domain 1.3 How XML Expresses Information 1.4 Patterns in XML 1.5 Common XML Information-Modeling Pitfalls 1.6 A Very Simple Way to Design XML 1.7 Conclusion Part II: Native XML Databases Chapter 2. Tamino-Software AG's Native XML Server 2.1 Introduction 2.2 Tamino Architecture and APIs 2.3 XML Storage 2.4 Querying XML 2.5 Tools 2.6 Full Database Functionality 2.7 Conclusion Chapter 3. eXist Native XML Database 3.1 Introduction 3.2 Features 3.3 System Architecture Overview 3.4 Getting Started 3.5 Query Language Extensions 3.6 Application Development 3.7 Technical Background 3.8 Conclusion Chapter 4. Embedded XML Databases 4.1 Introduction 4.2 A Primer on Embedded Databases 4.3 Embedded XML Databases 4.4 Building Applications for Embedded XML Databases 4.5 Conclusion Part III: XML and Relational Databases Chapter 5. IBM XML-Enabled Data Management Product Architecture & Technology 5.1 Introduction 5.2 Product and Technology Offering Summaries 5.3 Current Architecture and Technology 5.4 Future Architecture and Technology 5.5 Conclusion Notices Chapter 6. Supporting XML in Oracle9i 6.1 Introduction 6.2 Storing XML as CLOB 6.3 XMLType 6.4 Using XSU for Fine-Grained Storage 6.5 Building XML Documents from Relational Data 6.6 Web Access to the Database 6.7 Special Oracle Features 6.8 Conclusion Chapter 7. XML Support in Microsoft SQL Server 2000 7.1 Introduction 7.2 XML and Relational Data 7.3 XML Access to SQL Server 7.4 Serializing SQL Query Results into XML 7.5 Providing Relational Views over XML 7.6 SQLXML Templates 7.7 Providing XML Views over Relational Data 7.8 Conclusion Chapter 8. A Generic Architecture for Storing XML Documents in a Relational Database 8.1 Introduction 8.2 System Architecture 8.3 The Data Model 8.4 Creating the Database 8.5 Connecting to the Repository 8.6 Uploading XML Documents 8.7 Querying the Repository 8.8 Further Enhancements 8.9 Conclusion Chapter 9. An Object-Relational Approach to Building a High-Performance XML Repository 9.1 Introduction 9.2 Overview of XML Use-Case Scenario 9.3 High-Level System Architecture 9.4 Detailed Design Descriptions 9.5 Conclusion Part IV: Applications of XML Chapter 10. Knowledge Management in Bioinformatics 10.1 Introduction 10.2 A Brief Molecular Biology Background 10.3 Life Sciences Are Turning to XML to Model Their Information 10.4 A Genetic Information Model 10.5 NeoCore XMS* 10.6 Integration of BLAST into NeoCore XMS Conclusion Chapter 11. Case Studies of XML Used with IBM DB2 Universal Database 11.1 Introduction 11.2 Case Study 1: "Our Most Valued Customers Come First" 11.3 Case Study 2: "Improve Cash Flow" 11.4 Conclusion Notices Chapter 12. The Design and Implementation of an Engineering Data Management System Using XML and J2EE 12.1 Introduction 12.2 Background and Requirements 12.3 Overview 12.4 Design Choices 12.5 Future Directions 12.6 Conclusion Chapter 13. Geographical Data Interchange Using XML-Enabled Technology within the GIDB System 13.1 Introduction 13.2 GIDB METOC Data Integration 13.3 GIDB Web Map Service Implementation 13.4 GIDB GML Import and Export 13.5 Conclusion Chapter 14. Space Wide Web by Adapters in Distributed Systems Configuration from Reusable Components 14.1 Introduction 14.2 Advanced Concept Description: The Research Problem 14.3 Integration of Components with Architecture 14.4 Example 14.5 Future Generation NASA Institute for Advanced Concepts, Space Wide Web Research, and Boundaries 14.6 Advanced Concept Development 14.7 Conclusion Chapter 15. XML as a Unifying Framework for Inductive Databases 15.1 Introduction 15.2 Past Work 15.3 The Proposed Data Model: XDM 15.4 Benefits of XDM 15.5 Toward Flexible and Open Systems 15.6 Related Work 15.7 Conclusion Chapter 16. Designing and Managing an XML Warehouse 16.1 Introduction 16.2 Architecture 16.3 Data Warehouse Specification 16.4 Managing the Metadata 16.5 Storage and Management of the Data Warehouse 16.6 DAWAX: A Graphic Tool for the Specification and Management of a Data Warehouse 16.7 Related Work 16.8 Conclusion Part V: Performance and Benchmarks Chapter 17. XML Management System Benchmarks 17.1 Introduction 17.2 Benchmark Specification 17.3 Benchmark Data Set 17.4 Existing Benchmarks for XML 17.5 Conclusion Chapter 18. The Michigan Benchmark: A Micro-Benchmark for XML Query Performance Diagnostics 18.1 Introduction 18.2 Related Work 18.3 Benchmark Data Set 18.4 Benchmark Queries 18.5 Using the Benchmark 18.6 Conclusion Chapter 19. A Comparison of Database Approaches for Storing XML Documents 19.1 Introduction 19.2 Data Models for XML Documents 19.3 Databases for Storing XML Documents 19.4 Benchmarking Specification 19.5 Test Results 19.6 Related Work 19.7 Summary Chapter 20. Performance Analysis between an XML-Enabled Database and a Native XML Database 20.1 Introduction 20.2 Related Work 20.3 Methodology 20.4 Database Design 20.5 Discussion 20.6 Experiment Result 20.7 Conclusion Chapter 21. Conclusion References Contributors Editors Chapter 1: Information Modeling with XML Chapter 2: Tamino-Software AG's Native XML Server Chapter 3: eXist Native XML Database Chapter 4: Embedded XML Databases Chapter 5: IBM XML-Enabled Data Management Product Architecture and Technology Chapter 6: Supporting XML in Oracle9i Chapter 7: XML Support in Microsoft SQL Server 2000 Chapter 8: A Generic Architecture for Storing XML Documents in a Relational Database Chapter 9: An Object-Relational Approach to Building a High-Performance XML Repository Chapter 10: Knowledge Management in Bioinformatics Chapter 11: Case Studies of XML Used with IBM DB2 Universal Database Chapter 12: The Design and Implementation of an Engineering Data Management System Using XML and J2EE Chapter 13: Geographical Data Interchange Using XML-Enabled Technology within the GIDB System Chapter 14: Space Wide Web by Adapters in Distributed Systems Configuration from Reusable Components Chapter 15: XML as a Unifying Framework for Inductive Databases Chapter 16: Designing and Managing an XML Warehouse Chapter 17: XML Management System Benchmarks Chapter 18: The Michigan Benchmark: A Micro-Benchmark for XML Query Performance Diagnostics Chapter 19: A Comparison of Database Approaches for Storing XML Documents Chapter 20: Performance Analysis between an XML-Enabled Database and a Native XML Database Remember the name: eTutorials.org Copyright eTutorials.org 2008-2023. All rights reserved.