Using XQuery API for Java to Access Oracle XML DB

8 Using XQuery API for Java to Access Oracle XML DB

An explanation is given of how to use the XQuery API for Java (XQJ) to access Oracle XML DB.

Topics:

Introduction to Oracle XML DB Support for XQJ
XQuery API for Java (XQJ), also known as JSR-225, provides an industry-standard way for Java programs to access Extensible Markup Language (XML) data using XQuery. It lets you evaluate XQuery expressions against XML data sources and process the results as XML data.
Examples: Using XQJ to Query Oracle XML DB
Examples here show how you can use XQJ to query and retrieve data in Oracle XML DB.
XQJ Support for Oracle XML DB
The two Oracle XQJ implementations differ in some respects. Oracle XML DB support for XQJ is described.
XQJ Performance Considerations for Use with Oracle XML DB
To fetch a sequence of items from the database, use XQResultSequence method next() to retrieve a single item at a time; then use an XQItemAccessor method to fetch all data corresponding to that item.

8.1 Introduction to Oracle XML DB Support for XQJ

XQuery API for Java (XQJ), also known as JSR-225, provides an industry-standard way for Java programs to access Extensible Markup Language (XML) data using XQuery. It lets you evaluate XQuery expressions against XML data sources and process the results as XML data.

Oracle provides two XQuery engines for evaluating XQuery expressions: one in Oracle XML DB, for use with XML data in the database, and one in Oracle XML Developer's Kit (XDK), for use with XML data outside the database. (See Using the XQuery Processor for Java for information about the XQuery engine for XDK).

Oracle provides two different XQJ implementations for accessing these two XQuery engines. Both implementations are part of XDK, enabling you to use XDK to access XML data with a standard XQJ API whether that data resides in the database or elsewhere.

The queries executed by XQJ are written in standard World Wide Web Consortium (W3C) XQuery 1.0 language, as supported by Oracle XML DB. A typical use case for this feature is to access XML data stored in remote databases (in Oracle XML DB) from a local Java program.

General information about XQuery and XQJ is documented outside of this document.

Topics:

Prerequisites for Using XQJ to Access Oracle XML DB
You need Java Runtime Environment 1.6 to use XQJ with Oracle XML DB. You also need certain Java Archive (JAR) files to be either in your CLASSPATH environment variable or passed using command-line option classpath.

8.1.1 Prerequisites for Using XQJ to Access Oracle XML DB

You need Java Runtime Environment 1.6 to use XQJ with Oracle XML DB. You also need certain Java Archive (JAR) files to be either in your CLASSPATH environment variable or passed using command-line option classpath.

The JAR files are as follows:

The required JAR files listed in Introduction to the XQuery Processor for Java
jdbc/lib/ojdbc6.jar
rdbms/jlib/xdb6.jar

The directory paths for these JAR files are relative to the ORACLE_HOME directory of your database installation.

8.2 Examples: Using XQJ to Query Oracle XML DB

Examples here show how you can use XQJ to query and retrieve data in Oracle XML DB.

Example 8-1 shows how to use XQJ to query data from a table in Oracle XML DB. It uses the WAREHOUSES table in the Order Entry (OE) database sample schema. The OE sample schema contains XML documents with warehouse information in the WAREHOUSES table. The WAREHOUSES table contains an XMLType column warehouse_spec and other columns. (See the discussion about standard database schemas in Oracle XML DB Developer’s Guide for more information about the data used in this example.)

Specifically, Example 8-1 shows how to perform these steps:

Get an XQJ connection to Oracle XML DB.

Every program using XQJ to connect to Oracle XML DB must first create an OXQDDataSource object. Then, OXQDDataSource must be initialized with the required property values before getting an XQJ connection to the Oracle XML DB instance.
Prepare an XQuery expression.
Submit the XQuery expression for evaluation.
Print each item in the resulting XQuery sequence.

In Example 8-1, the XQuery expression accesses the WAREHOUSES table through the use of the Universal Resource Identifier (URI) scheme oradb. (See the discussion about the URI scheme oradb in Oracle XML DB Developer’s Guide for more information about using XQuery with XML DB to query table or view data.)

Example 8-1 also shows how to bind external variable values in XQJ. The query has an external variable $x, which is used to filter the returned rows from the WAREHOUSES table, by WAREHOUSE_ID.

Example 8-1 generates this output (reformatted for better readability):

<Warehouse><Building>Owned</Building><Area>25000</Area><Docks>2</Docks>
 <DockType>Rearload</DockType><WaterAccess>Y</WaterAccess>
 <RailAccess>N</RailAccess><Parking>Street</Parking>
 <VClearance>10 ft</VClearance></Warehouse>

<Warehouse><Building>Rented</Building><Area>50000</Area><Docks>1</Docks>
 <DockType>Sideload</DockType><WaterAccess>Y</WaterAccess>
 <RailAccess>N</RailAccess><Parking>Lot</Parking>
 <VClearance>12 ft</VClearance></Warehouse>

Example 8-2 shows how to use XQJ to retrieve data from the Oracle XML DB repository. This example assumes that two files, depts.xml and emps.xml, have been uploaded into the XML DB repository under the folder /public. For example, you can use FTP to upload the two files into the Oracle XML DB repository. (See the discussion about using the Oracle XML DB repository in Oracle XML DB Developer’s Guide for more information about storing data in and using the Oracle XML DB Repository.)

The content of depts.xml is:

depts.xml:
 
<?xml version="1.0"?>
     <depts>
       <dept deptno="10" dname="Administration"/>
       <dept deptno="20" dname="Marketing"/>
       <dept deptno="30" dname="Purchasing"/>
     </depts>

The content of emps.xml is:

emps.xml:
 
<?xml version="1.0"?>
   <emps>
    <emp empno="1" deptno="10" ename="John"  salary="21000"/>
       <emp empno="2" deptno="10" ename="Jack" salary="310000"/>
       <emp empno="3" deptno="20" ename="Jill" salary="100001"/>
     </emps>

You can use the fn:doc and fn:collection functions to query the data in the Oracle XML DB repository with XQuery. Example 8-2 shows how to use the fn:doc function within XQuery to access the repository. (See the discussion about querying XML data in the Oracle XML DB repository in Oracle XML DB Developer’s Guide for more information about using these XQuery functions.)

Example 8-2 generates this output:

<emp ename="Jack" dept="Administration"/>
<emp ename="Jill" dept="Marketing"/>

Example 8-1 Using XQJ to Query an XML DB Table with XQuery

import oracle.xml.xquery.xqjdb.OXQDDataSource;
 
import javax.xml.xquery.XQItemType;
import javax.xml.xquery.XQResultSequence;
import javax.xml.xquery.XQConnection;
import javax.xml.xquery.XQPreparedExpression;
import javax.xml.namespace.QName;
 
public class example1
{
  public static void main(String argv[])
  {
    try
    {
      // Create a new OXQDDataSource for connecting to Oracle XML DB
      OXQDDataSource oxqDS = new OXQDDataSource();
      // Set appropriate connection information for the database instance.
      // Must use the thin driver
      oxqDS.setProperty("driver", "jdbc:oracle:thin");
      oxqDS.setProperty("dbusername", "oe");
      oxqDS.setProperty("dbpassword", "oe");
      // Machine hostname
      oxqDS.setProperty("dbserver", "myserver");
      // Database instance port number
      oxqDS.setProperty("dbport", "6479");
      // Database instance port number
      oxqDS.setProperty("serviceName", "mydbinstance");
      XQConnection conn = oxqDS.getConnection();
      XQItemType  itemTypeInt = conn.createAtomicType(XQItemType.XQBASETYPE_INT);
      XQPreparedExpression expr = conn.prepareExpression("declare variable $x as
        xs:int external; for $i in fn:collection('oradb:/OE/WAREHOUSES') where
          $i/ROW/WAREHOUSE_ID < $x return $i/ROW/WAREHOUSE_SPEC/Warehouse");
      expr.bindInt(new QName("x"), 3, itemTypeInt);
      XQResultSequence xqSeq = expr.executeQuery();
      while (xqSeq.next())
         System.out.println (xqSeq.getItemAsString(null));
     }
     catch (Exception e)
     {
      e.printStackTrace();
     }
  }
}

Example 8-2 Using XQJ to Query the XML DB Repository with XQuery

import oracle.xml.xquery.xqjdb.OXQDDataSource;
 
import javax.xml.xquery.XQItemType;
import javax.xml.xquery.XQResultSequence;
import javax.xml.xquery.XQConnection;
import javax.xml.xquery.XQPreparedExpression;
import javax.xml.namespace.QName;

public class example2
{
  public static void main(String argv[])
  {
    try
    {
      // Create a new OXQDDataSource for connecting to Oracle XML DB
      OXQDDataSource oxqDS = new OXQDDataSource();
      // Set appropriate connection information for the database instance.
      // Must use the thin driver
      oxqDS.setProperty("driver", "jdbc:oracle:thin");
      oxqDS.setProperty("dbusername", "oe");
      oxqDS.setProperty("dbpassword", "oe");
      // Machine hostname
      oxqDS.setProperty("dbserver", "myserver");
      // Database instance port number
      oxqDS.setProperty("dbport", "6479");
      // Database instance port number
      oxqDS.setProperty("serviceName", "mydbinstance");
      XQConnection conn = oxqDS.getConnection();
      XQPreparedExpression expr = conn.prepareExpression("for $e in
        doc(\"/public/emps.xml\")/emps/emp let $d :=
        doc(\"/public/depts.xml\")//dept[@deptno = $e/@deptno]/@dname where
        $e/@salary > 100000 order by $e/@empno return 
        <emp ename=\"{$e/@ename}\" dept=\"{$d}\"/>");
      XQResultSequence xqSeq = expr.executeQuery();
      while (xqSeq.next())
         System.out.println (xqSeq.getItemAsString(null));
     }
     catch (Exception e)
     {
      e.printStackTrace();
     }
  }
}

8.3 XQJ Support for Oracle XML DB

The two Oracle XQJ implementations differ in some respects. Oracle XML DB support for XQJ is described.

Using the XQuery Processor for Java provides information about using XQJ to access the mid-tier XQuery engine.

Table 8-1 describes the OXQDDataSource properties to be used for connection to Oracle XML DB. To create an XQJ connection to Oracle XML DB, you must set the values for these properties. You must set either the dbname or the serviceName property value, and all the other OXQDDataSource property values listed in Table 8-1.

Table 8-1 OXQDDataSource Properties

Property	Value	Get Method	Set Method
`driver`	`jdbc:oracle:thin`	`getDriver`	`setDriver`
`dbusername`	Database schema (user) name	`getDBUserName`	`setDBUserName`
`dbpassword`	Password for database schema	`getDBPassword`	`setDBPassword`
`dbserver`	Host name for the database instance	`getDBServer`	`setDBServer`
`dbport`	Port number of the database instance for XQJ connection	`getDBPort`	`setDBPort`
`dbname`	Database instance name (service id)^Foot 1	`getDBName`	`setDBName`
`serviceName`	Service name^Footref 1	`getServiceName`	`setServiceName`

^Footnote 1

You can identify the database using either the service id or the service name.

Table 8-2 describes the Oracle XML DB support for optional XQJ features.

Note:

Oracle XML DB support for some XQJ features differs from their support by the mid-tier XQuery engine. In particular, the Oracle XML DB XQJ implementation does not support the use of user-defined types.

Table 8-2 Oracle XML DB Support for Optional XQJ Features

XQJ Feature	Oracle XML DB Support
Class name of `XQDataSource` implementation	`oracle.xml.xquery.xqjdb.OXQDDataSource`
JDBC connections	Not supported.
Properties defined on `OXQDDataSource` (connection information)	See Table 8-1.
Commands	Not supported.
`XQPreparedExpression.cancel` (cancelling of query execution)	Not supported.
Serialization	Only parameter `method` with value `xml` and parameter `encoding` with value `UTF-8` or `UTF-16`.
Additional StAX and SAX events	Not supported.
User-defined schema types	Not supported.
Node identity, document order, and full-node context preservation when a node is bound to an external variable	Not supported.
Login timeout	Not supported.
Transactions	Not supported.
Behavior of `XQItemAccessor` method `getNodeUri()` when the input node is not a document node	Return `NULL`.
Behavior of `XQItemType` method `getTypeName()` for anonymous types	Return `false`.
Behavior of `XQItemType` method `getSchemaURI()`	Return `NULL` or the schema URI provided during type creation. Currently, the Oracle XML DB XQJ implementation does not use the schema URI to get type information, and user-defined types are not supported.
Behavior of `XQDataFactory` methods `createItemFromDocument()` and `bindDocument()` if the input is not a well-formed XML document	Raise an exception.
Additional error codes returned from `XQQueryException`	Not supported.
Interfaces `ConnectionPoolXQDataSource`, `PooledXQConnection`, `XQConnectionEvent`, `XQConnectionEventListener`	Not supported.
XQDataSource.getConnection( java.sql.Connection)	Not supported. (JDBC connections are not supported.)
XQDataSource.getConnection( java.lang.String, java.lang.String)	Same as `getConnection()` with no arguments: the arguments are ignored.

Topics:

Other Oracle XML DB XQJ Support Limitations
The limitations of Oracle XML DB support for XQJ are described. None of them apply to mid-tier XQuery engine support for XQJ.

8.3.1 Other Oracle XML DB XQJ Support Limitations

The limitations of Oracle XML DB support for XQJ are described. None of them apply to mid-tier XQuery engine support for XQJ.

Oracle XML DB support for XQJ is limited in these ways:

All Oracle XML DB XQuery support limitations apply to Oracle XML DB support for XQJ as well.
Only the XDK Document Object Model (DOM) is supported. Use of any other DOM can cause errors.
Do not expect the Oracle XML DB XQJ implementation to be interoperable with another XQJ implementation, including the XDK Java implementation of XQJ. (See the XQJ standard (JSR-225) for the meaning of "interoperable".)
XQDataSource methods getLogWriter and setLogWriter have no effect (they are ignored).
XQStaticContent methods getBoundarySpacePolicy, setBoundarySpacePolicy, getDefaultCollation, and setDefaultCollation have no effect (they are ignored).
The copy namespaces mode for XQStaticContent methods setCopyNamespacesModPreserve and setCopyNamespacesModeInherit has no effect (it is ignored). The values used are always preserve and inherit, respectively.
Use of XQDynamicContext methods to bind DocumentFragment objects is not supported.
Values of type xs:duration are not supported. Using an XQDynamicContext method to bind xs:duration, or accessing an xs:duration value, raises an error.
The year of a xs:date, xs:dateTime, xs:gYear, and xs:gYearMonth value must be from -4712 to 9999, inclusive. Using a year outside this range can raise an error or produce unpredictable results.

8.4 XQJ Performance Considerations for Use with Oracle XML DB

To fetch a sequence of items from the database, use XQResultSequence method next() to retrieve a single item at a time; then use an XQItemAccessor method to fetch all data corresponding to that item.

This provides better performance than using these whole-sequence fetch methods, which each materialize the entire sequence before returning any data.

getSequenceAsStream()
getSequenceAsString(java.util.Properties props)
writeSequence(java.io.OutputStream os, java.util.Properties props)
writeSequence(java.io.Writer ow, java.util.Properties props)
writeSequenceToResult(javax.xml.transform.Result result)
writeSequenceToSAX(org.xml.sax.ContentHandler saxhdlr)

For example, if you invoke getSequenceAsStream(), all of the XQuery result sequence data is fetched from the database before the XMLStreamReader instance that is built from it is returned to your program.

Be aware also that items themselves are not streamable: the item accessor methods always materialize an entire item before outputting any part of it.

For inputting, all bind methods defined on XQDynamicContext fully materialize the input data before passing it to the database.

For example, when you invoke bindDocument(javax.xml.namespace.QName varName, javax.xml.stream.XMLStreamReader value, XQItemType type), all the data that is referenced by the input XMLStreamReader instance is processed before the external XQuery variable is bound to it.