6.3 XMLType

Since XML documents can be considered a new structured data type in object-relational databases, new advantages arise, which are discussed in Klettke and Meyer (2000), Banerjee et al. (2000), and Cheng and Xu (2000a) in general.

6.3.1 Object Type XMLType

Oracle9i follows this approach and introduces a new object type XMLType. The main purpose of XMLType is to encapsulate the CLOB storage and to provide XPath-based methods to ease the handling of XML documents. XMLType can be used like any other Oracle type in tables. XMLType provides several methods for processing XML documents:

STATIC createXML(xml VARCHAR | CLOB)
MEMBER getClobVal() RETURN CLOB
MEMBER getStringVal() RETURN VARCHAR
MEMBER getNumberVal() RETURN NUMBER
MEMBER isFragment() RETURN NUMBER

The static method createXML takes an XML string or a CLOB and creates an XMLType object, thereby checking well-formedness but not the validity with respect to a DTD or an XML schema. The methods getClobVal and getStringVal return the XMLType content in a serialized format. getNumberVal yields a NUMBER value and requires the text to be numeric. Hence, getNumberVal cannot be applied to elements of the form <CNo> "10" </CNo>; the XPath function text() must be used beforehand to extract the numeric value "10". Two further methods of XMLType benefit from an XPath subset for querying:

MEMBER existsNode(VARCHAR xpath) RETURN NUMBER applied over the XMLType document checks if an XPath determines any valid nodes.
MEMBER extract(VARCHAR xpath) RETURN XMLType extracts fragments out of XMLType documents and returns them as an XMLType object.

The function existsNode is useful to ask for the existence of elements, while extract returns a fragment qualified by XPath. Hence, existsNode covers the XML-specific parts of OracleText. The function extract provides parser functionality, which is not available in OracleText. Moreover, it is important to note that both functions do not demand OracleText or explicit indexing.

Let us now assume a table XMLTypeTab (id:INTEGER, txt: XMLType) using an XMLType column instead of a CLOB. Listing 6.7 presents an example of how to handle XMLType objects.

Listing 6.7 XMLType Example

INSERT INTO XMLTypeTab
VALUES(1, SYS.XMLType.createXML
     ('<?xml version="1.0"?> <Customer> <CNo> 10 </CNo><Name>
     . . . 
     </Customer>')

An XML document, given as a string, is created with createXML, thereby checking the well-formedness?this is the easiest way to create XMLType objects. Later we describe another way, namely SQL functions that return XMLType objects by extracting information from a given XMLType or by using relational data.

Listing 6.8 shows an example how to extract information.

Listing 6.8 Qualify XML Documents

SELECT x.txt.extract('Customer//Phone')
FROM XMLTypeTab x
WHERE x.txt.existsNode('Customer//Order') = 1
AND x.txt.extract('/Customer/CNo/text()').getNumberVal() = 10

This example shows how to qualify XML documents and how to extract parts. A SELECT-FROM-WHERE is used to formulate the overall query. The query computes a list of phone numbers for customer 10. The extract function in SELECT uses an XPath to extract any occurrence of the Phone-tag beneath a Customer-tag. existsNode requires the existence of an Order-tag for the customer. Owing to the XPath function text() and the XMLType method getNumberVal(), XPath and SQL can be combined in conditions. Extracting the CNo-element, text() computes the text of that element, whereupon getNumberVal() yields an integer that can be used in SQL conditions.

The same type of extract-condition is possible in UPDATE and DELETE statements?for example, to delete the documents that contain a customer with CNo=10 having placed an order.

There are some restrictions on XPath expressions. The following features are currently not available:

conditions in [ ]
functions count(), sibling:: etc.

That is, only path expressions with / and //, possibly ending with text(), wildcards, attribute access, and indexing [ i] are currently allowed. However, text indexes can be defined for XMLType objects, too, so that the full OracleText functionality becomes available.

The method extract is a simple and easy-to-use substitute for an XML parser. Combined with getNumberVal and getStringVal, it can especially be employed to extract data from XML and to store in structured tables. Listing 6.9 shows an example.

Listing 6.9 Extract Method Example

INSERT INTO Customer (No, Name, Address)
(SELECT x.extract('/Customer/CNo/text()').getNumberVal(),
        x.extract('/Customer/Name/Firstname/text()').getStringVal()
        || ' ' ||
        x.extract('/Customer/Name/Lastname/text()').getStringVal(),
        x.extract('/Customer/Address/Zip/text()').getStringVal()
        || ' ' ||
        x.extract('/Customer/Address/City/text()').getStringVal()
        || ' ' ||
        x.extract('/Customer/Address/Street/text()').getStringVal()
        || ' ' ||
        x.extract('/Customer/Address/Houseno/text()').getStringVal()
FROM XMLTypeTab x

Here, parts of the XML document are extracted and stored in a table Customer (No INT, Name VARCHAR, Address VARCHAR). The firstname and lastname values are concatenated ('||') to a single name, just as the parts (Zip, City, etc.) of an address are.

6.3.2 Processing of XMLType in Java

The JAR archive xdb_g.jar contains a Java class oracle.xdb.XMLType that directly corresponds to the object type XMLType. This Java class possesses the same functionality as the SQL object type. All the XMLType methods are also available for the Java class?for example, java.lang.String getStringVal(), oracle.sql.CLOB getClob() in the same manner. However, both methods createXML(Connection, oracle.sql.clob) and createXML(Connection, String) require a JDBC database Connection as a first parameter.

For example, the processing of XMLType objects in Java is shown in Listing 6.10.

Listing 6.10 XMLType Objects and Java

OracleResultSet ors = (OracleResultSet)stmt.executeQuery
                      ("SELECT x.txt.extract( . . . ) FROM
                       XMLTypeTab x"); . . .
while (ors.next()) {
   oracle.xdb.XMLType t = (oracle.xdb.XMLType) ors.getOPAQUE(1);
   . . .  handling of XMLType t in Java with getStringVal, extract
                      etc.  . . .
}

OracleResultSet is a subclass of JDBC's ResultSet, enhancing the original JDBC methods. OracleResultSet has a method OPAQUE getOPAQUE(int) that can be used to read XMLType objects from the database. Similarly, the JDBC Statement classes possess a setObject method to pass XMLType Java objects to statements as parameters. In order to handle null values and to register output parameters, there is also a type constant OracleTypes.OPAQUE.