Oracle Open Sourcing XQilla XQuery Engine

About a month ago, Justin Kestelyn (Oracle Technology Network) posted a link to a press release announcing Oracle's release of the XQilla XQuery Engine under an Apache License. I have blogged several times regarding XQuery (and especially about Oracle XQuery implementations) and am excited about an open source XQuery engine being available.

Oracle's FOSS (Free and Open Source Software) page already has a link to another Oracle web page focusing on the XQilla XQuery Engine. This has been one of several recent moves by Oracle to actively participate in the open source implementation of standards movement. Other relatively recent moves in this space have included ADF Faces, the JPA 1.0 reference implementation TopLink Essentials, the open sourcing of the full-fledged TopLink product, and proposal for and support of the JPA 2.0 reference implementation EclipseLink.

The following are other links to articles and blogs on the open sourcing of XQilla.

* Oracle Open Sources XQuilla XQuery (note spelling error that I keep wanting to make as well when I type it -- the "u")

* Oracle Announces XQilla

* Oracle's XQilla Open-Source Contribution and the Art of PR

RMOUG TD 2008 Answers to XQuery Questions

I finished my two presentations at Rocky Mountain Oracle Users Group (RMOUG) Training Days 2008 over the past two days. The group that attended the Apache POI presentation was relatively small and did not have many questions during or after the presentation. The group at the XQuery presentation, however, was much larger and had many questions during and after the presentation. This blog entry covers some of these questions and the answers.

Before getting into some of the questions that were asked along with answers to them and additional references associated with them, I'd like to thank all those who attended my two presentations and especially thank those who asked questions.

How do you use Oracle's proprietary ora:view function with more than one table (joins)?

To this question, I answered that the only way that I could think of and had seen to do this is to use ora:view on a Oracle database view. This appears to be the appropriate answer as documented in the Using XQuery with Oracle XML DB document.

Can you query the contents of Microsoft Office documents with XQuery?

It is possible to use XQuery to query the contents of Microsoft Office 2007 documents (or documents saved in the Office Open XML format using the available converters for older versions of Office applications). In fact, this is documented in the article XQuery Your Office Documents. Note, however, that this requires that the XQuery engine be able to open ZIP files and I have not been able to find a way to do that automatically with Oracle's XQuery implementation. One could still open the Office Open XML ZIP file in another way (using ZIP utility or JAR or similar mechanism) and then XQuery the contents.

The meeting participant who asked this question needs to query Office applications data prior to the Office Open XML format, so I told her that XQuery will not be able to help here. While my other topic at this conference (Apache POI) is useful for reading Excel, it does not work natively out of the database like XQuery does with Oracle's XQuery implementation.

How do you use XQuery with an XMLType Database Column?

Although I had many XQuery examples in my presentation, I did not specifically demonstrate the use of the SQL/XML standard XMLQUERY on an XMLTYPE database column. Fortunately, a good example of this is demonstrated as Example 18-8 in the Using XQuery with XMLType Data section of the Using XQuery with Oracle XML DB chapter of the Oracle XML DB Developer's Guide 11g. The key to this is the use of the PASSING clause.

How do you terminate an XQuery expression?

I think I may have misunderstood this question and given an answer to a different question. I think what the attendee was asking is how one could have multiple XQuery statements in a single document running procedurally. It is important to note that an XQuery Scripting Extension is currently under work in the Worldwide Web Consortium (W3C). The XQuery Prototype with XQLPlus README document discusses using an XQuery .xql file. Perhaps the best example of complex XQuery used similar to XSLT is the article Generating XML and HTML with XQuery. This example uses XQuery custom functions embedded within a master XQuery expression. In other words, with XQuery, nesting XQuery expressions is the approach often used to build elaborate transformations.

Finally, the Querying XMLType Data section of the article Querying, Constructing, and Transforming XML with XQuery also shows how nested XQuery expressions can be used in transformation.

Is Apache POI Related to or Dependent On Apache HTTP Server?

For those of us who use several Apache products, this is an easy question and answer. However, I thought it was a good question that reminds me that many in the IT community, especially those outside of the Java development community, may not be as familiar with the Apache Software Foundation. Just as Oracle Corporation is still most often associated with database products by many DBAs and developers, it is likely that Apache implies HTTP server (or perhaps Tomcat servlet container) to many people as well. I pointed out that not only is Apache POI not dependent on Apache HTTP server being present, but that they in fact have very little to do with each other besides their common "Apache" name.

---

UPDATE (16 February 2007): The following are some other accounts in the blogosphere regarding recently concluded RMOUG Training Days 2008.

RMOUG Training Days 2008 Starting -- Alex Gorbachev
http://www.pythian.com/blogs/825/rmoug-training-days-2008-starting

First Day at RMOUG -- Babette Turner-Underwood
http://www.pythian.com/blogs/827/first-day-at-rmoug

RMOUG Day 2!! -- Babette Turner-Underwood
http://www.pythian.com/blogs/829/rmoug-day-2

RMOUG 2008 is Over -- Alex Gorbachev
http://www.pythian.com/blogs/831/rmoug-2008-is-over

George Trujillo on RMOUG Training Days 2008
http://trubix.blogspot.com/
* "RMOUG - Excellent Ending, Now on to Collaborate 2008 in Denver"
* "RMOUG Training Days - February 13, 14 2008 in Denver, CO"
* "RMOUG 2008: MySQL and Oracle Fusion Middleware Presentations"

Can a Performance Tuning Tool be Complete?
http://www.battleagainstanyguess.com/2008/02/can-a-performance-tuning-tool-be-complete/

Oracle XQuery and the fn:doc-available Function

When using Oracle XQuery implementation, one important point to understand is that the fn:doc function returns an empty sequence if the specified resource is not found. This would be a problem if there was no way of knowing the difference between a resource that could not be found and an XQuery that did not match any results. Fortunately, Oracle's implementation of fn:doc-available function can be used to test for the availability of the resource. The screen shot shown next (click on it to see larger version) shows use of fn:doc-available against a URL with file:/// that returns true because the resource is found. However, as shown in the screen shot, when I fail to specify the file:/// protocol, false is returned. I also showed trying to use fn:doc against this same URL to demonstrate the return of the empty sequence rather than an error message. This behavior is as documented in Using XQuery with Oracle XML DB (Chapter 18 of Oracle XML DB Developer's Guide).



The screen shot specifically demonstrates use of XQLPlus to show use of fn:doc and fn:doc-available, but this behavior should be the same for all Oracle XQuery implementations.

Had I included the file:/// protocol in the URI to fn:doc, but with an invalid file location, an error would have been returned as shown in the next screen shot image:



So, Oracle XQuery implementations that are told to look for a file (with the file:/// protocol) and cannot find that file will report an error. On the other hand, if the URL resolves to something other than the file system (such as the default XML DB repository) with a non-valid value, an empty sequence is returned. The function doc-available returns false in both cases (file not found and non-file URI with no matching resource). Note that the XQuery implementations can also find documents in the database's XML DB repository and this is why file:/// is not a default (and C:\ cannot be found in the repository).

Use of doc-available is also recommended before fn:doc that accesses file system files (with file:///) because doc-available returns "false" for files that are wrong format (such as binary files or files with encoding other than UTF-8) and for files that are text but not XML. If fn:doc is used directly on the binary files an exception will be thrown with the message "Invalid UTF8 encoding." Likewise, if fn:doc is used directly on a non-XML text file, an exception will be thrown with the message "Start of root element expected." Using fn:doc-available in a conditional before using fn:doc in an XQuery script helps avoid these exceptions.

Specification details on fn:doc and fn:doc-available can be found in the XQuery 1.0 and XPath 2.0 Functions and Operators document.

XQuery and the Oracle XML DB Repository

In my previous blog entry Specifying a Document in XQLPlus, I discussed the importance of specifying the file:// protocol in front of a file system path when trying to access an external file using Oracle XML DB's implementation of the XQuery standard function fn:doc. The reason that you need to specify the file:// protocol to explicitly access an external file in the file system is that Oracle database provides an Oracle XML DB repository with file-like path addresses and the file:// designation instructs the XQuery engine to look at an external file rather than looking in the repository.

The Oracle documentation Using XQuery with Oracle XML DB (part of the Oracle XML DB Developer's Guide) and the Oracle Technology Network (OTN) article Querying, Constructing, and Transforming XML with Oracle XQuery, and OTN article XML: Getting to XML each discuss this repository and how it can be used and accessed.

Perhaps the most definitive source of information on the Oracle XML DB repository is in Chapter 20 ("Accessing Oracle XML DB Repository Data") of the Oracle XML DB Developer's Guide (11g PDF). This section explains the need to create new "folders" in the repository before trying to insert documents into them and warns against placing anything under the /sys portion of the repository.

An easy way to see the difference between accessing XML in the Oracle XML DB repository using fn:doc and accessing an external XML file using fn:doc is to do both using the XQLPlus tool. To demonstrate accessing both external files and repository XML with fn:doc, I first need to place XML in both locations.

For the XML file in the file system, I will be using a subset of an XML file used in a previous blog entry. The file will be called C:\xquery\xmlSource\planets-nomoons.xml. That file is shown next:

<?xml version = '1.0'?>
<!-- Note that all data here is not meant to be factual, but is instead intended
     to illustrate XQuery principles. -->
<Planets>
  <Planet name="Mercury"
          minDistanceFromSunMK="46"
          maxDistanceFromSunMK="70"
          class="planet" />
  <Planet name="Venus"
          minDistanceFromSunMK="108"
          maxDistanceFromSunMK="109"
          class="planet" />
  <Planet name="Earth"
          minDistanceFromSunMK="146"
          maxDistanceFromSunMK="152"
          class="planet" />
  <Planet name="Mars"
          minDistanceFromSunMK="205"
          maxDistanceFromSunMK="249"
          class="planet" />
  <Planet name="Jupiter"
          minDistanceFromSunMK="741"
          maxDistanceFromSunMK="817"
          class="planet" />
  <Planet name="Saturn"
          minDistanceFromSunMK="1350"
          maxDistanceFromSunMK="1500"
          class="planet" />
  <Planet name="Uranus"
          minDistanceFromSunMK="2700"
          maxDistanceFromSunMK="3000"
          class="planet" />
  <Planet name="Neptune"
          minDistanceFromSunMK="4460"
          maxDistanceFromSunMK="4540"
          class="planet" />
  <Planet name="Pluto"
          minDistanceFromSunMK="7376"
          maxDistanceFromSunMK="4437"
          class="dwarf" />
</Planets>

With the above XML saved to the file, I'll now move onto placing this XML into the repository using the built-in PL/SQL stored function DBMS_XDB.createResource. Alternate ways of accessing the Oracle XML DB Repository include via Java, SQL, FTP, and HTTP/servlets. The code below, when run in SQL*Plus using PL/SQL, places the same XML into the XML DB repository.

DECLARE
  populateResponse BOOLEAN;
  planetsXmlString VARCHAR2(10000) :=
    '<?xml version = "1.0"?>
<Planets>
  <Planet name="Mercury"
          minDistanceFromSunMK="46"
          maxDistanceFromSunMK="70"
          class="planet" />
  <Planet name="Venus"
          minDistanceFromSunMK="108"
          maxDistanceFromSunMK="109"
          class="planet" />
  <Planet name="Earth"
          minDistanceFromSunMK="146"
          maxDistanceFromSunMK="152"
          class="planet" />
  <Planet name="Mars"
          minDistanceFromSunMK="205"
          maxDistanceFromSunMK="249"
          class="planet" />
  <Planet name="Jupiter"
          minDistanceFromSunMK="741"
          maxDistanceFromSunMK="817"
          class="planet" />
  <Planet name="Saturn"
          minDistanceFromSunMK="1350"
          maxDistanceFromSunMK="1500"
          class="planet" />
  <Planet name="Uranus"
          minDistanceFromSunMK="2700"
          maxDistanceFromSunMK="3000"
         class="planet" />
  <Planet name="Neptune"
          minDistanceFromSunMK="4460"
          maxDistanceFromSunMK="4540"
          class="planet" />
  <Planet name="Pluto"
          minDistanceFromSunMK="7376"
          maxDistanceFromSunMK="4437"
          class="dwarf" />
</Planets>';
BEGIN
  populateResponse := DBMS_XDB.createResource(
    '/public/planetswithnomoons.xml',
    planetsXmlString );
COMMIT;
END;
/

Running the above in SQL*Plus places the planets XML source into the XML DB repository. I can confirm that this was successfully placed into the repository with the following SQL*Plus query using the XMLQuery SQL/XML function (click on image to see larger version):



With the Planets XML in both a file and in the XML DB repository, I can now run XQuery expressions over both sources via XQLPlus using the fn:doc command.

The next image (click on it to see larger version) shows the output from running a simple XQuery expression against the XML stored in the repository. The example also demonstrates that a SQL connection is required and that set server on must be executed within XQLPlus before running the command to access the repository. The SQL connection and the server setting will not be required when we access an external file.



The next example demonstrates the identical XQuery expression with the one change being the specification of the document over which to bind. In the previous example, it was a repository item and no protocol was required. In the next example, the file:/// protocol is required to access an external file on the file system. No SQL connection is necessary for this external file example and I don't need to set server on either.



When running XMLPlus as shown in the last example, I really didn't need to include the Oracle JDBC JAR on the classpath (even though I did to be consistent with the previous example). In other words, XQLPlus needs the JDBC driver class for accessing the XML in the XML DB repository, but does not need it for accessing XML in an external file. As pointed out earlier, the XML DB repository access also required a sqlconn SQL/JDBC connection to be specified and required the setting of set server on. XQLPlus access of the external file did not require the JDBC driver, the SQL/JDBC connection, or the turning on of the "server" option.

Finally, note the output differences between running these two nearly identical examples in XQLPlus. These differences are most likely due to the XML representation stored in the respective sources (repository versus external text/XML file).

Oracle SQL*Plus XQUERY Command

In previous blog entries, I wrote about Oracle's support for XQuery with the XQLPlus XQuery command-line editor and Oracle's XQuery API for Java (XQJ) implementation (OJXQI). In this blog entry, I'm going to focus on using the SQL*Plus XQUERY command to simply run XQuery expressions in SQL*Plus. While this is not a standard command like the SQL/XML functions XMLQUERY and XMLTABLE, it is really easy to use and a nice way to do quick XQuery work in SQL*Plus.

The SQL*Plus User's Guide and Reference discusses use of SQL*Plus XQUERY in both the Release 10.2 and Release 11.1 (11.1 PDF) versions of this document. In this blog entry, I'll show some examples that demonstrate the topics discussed in that manual and add some details that I have not seen documented in either version.

The SQL*Plus XQUERY command can be executed without any interaction with database tables or views because XQuery expressions do no necessary rely upon underlying database or even XML sources to run properly. For example, the next screen shot (click on the image to see a larger version of it), shows a simple XQuery expression run with the XQUERY command in SQL*Plus and the XQuery result expressed.



Note that the XQuery expression shown in the example above does not rely on any data from the database. For this blog entry, to keep things simple, I'll use XQuery expressions that do not interact with the database. There are many examples in the Oracle documentation that show how to use SQL*Plus XQUERY to interact with the database. I want to focus in this blog on the configurable settings available for XQUERY instead.

The next screen shot (click on it to see larger version) demonstrates a perfectly valid XQuery expression being supplied via the XQUERY command, but the result does not show the full entered expression.



To see the entire results of the XQuery expression in the example above, the SQL*Plus setting LONG needs to be set appropriately. Note that this setting is not specific to XQuery or XQUERY, but is instead a standard setting in SQL*Plus. The next screen show shows how using SET LONG can overcome the problem. In this case, I am setting LONG to 200, enough for this simple example. The default for this setting is so low that the documentation recommends that this LONG setting usually needs to be set for using XQUERY.



Similarly to the use of SET LONG shown above, other standard SQL*Plus settings can be used in conjunction with the XQUERY command. For example, SET LINESIZE can be used for individual lines that are longer than the default of eighty characters. Likewise, SET PAGESIZE can also be used.

A standard SQL*Plus setting that I particularly like using with my XQUERY commands is the command SET HEADING OFF. As with other non-XQUERY SQL*Plus queries, the setting of HEADING to OFF removes the printed header on top of the returned query results. In the case of the XQUERY command, the normally returned header (as shown in the screen shots above) is "Result Sequence" with a line separator formed by use of consecutive hyphens (---). As the next screen shot demonstrates, this heading is turned off with SET HEADING OFF.



There may be times where we wish to keep the header (not use SET HEADING OFF or explicitly use SET HEADING ON), but wish to change the heading from "Result Sequence" to something of our own choosing. The 10.2 SQL*Plus User's Guide and Reference states that the heading for the returned XQUERY results can be changed using the COLUMN command with the column column_value being the one whose HEADING is changed. However, I cannot find any column_value column in the 11.1 database, nor do I see any mention of changing this header in the 11.1 SQL*Plus User's Guide and Reference.

Fortunately, it is easy to determine which column needs to have its heading changed to print out our desired heading on XQUERY results. The simple way to determine this is to enter COLUMN (lowercase or uppercase) in SQL*Plus without any arguments and look through the list of set columns for a likely candidate. The next screen snapshot shows the relevant portion of this.



We don't have to look long in the results returned from running column in SQL*Plus without arguments to see the appropriate column to set. It is the first one returned! Based on this, we can see result_plus_xquery to display any heading we like. To do this, we can use either

column result_plus_xquery heading "We like XQUERY!"

or

column result_plus_xquery heading "We like XQUERY!" ON

The first shown command will only lead to the designated heading be printed above XQUERY results if the heading happens to be turned on already (the default). However, the second command does the same thing and ensures that the heading will be printed by turning it on.

The next screen snapshot demonstrates use of the second version. Note how the heading is shown again, but is no longer the default "Result Sequence," but is instead our specified custom heading "We like XQUERY!"

Up to this point, I have only used standard SQL*Plus settings to change how the XQUERY command displays its results in SQL*Plus. There are also XQUERY-specific settings as well. Due to the rather sizable length of this entry already, I'll only list these XQUERY-specific SQL*Plus settings here and may discuss them in greater details in the future (though the documentation covers them fairly thoroughly already).

The XQUERY-specific SQL*Plus settings are BASEURI, ORDERING, NODE, and CONTEXT. These are each set by starting the set command with SET XQUERY and then supplying one of the four specific setting names and its value to be set. The default settings for these four XQUERY-specific SQL*Plus settings can be seen with the SHOW XQUERY command as demonstrated in the next screen snapshot.



As the above screen snapshot indicates, BASEURI and CONTEXT have empty strings as their defaults. You must go to the documentation to find out what DEFAULT means for the other two settings. The default setting for NODE is BYVALUE, meaning that newly created nodes will not maintain identity and will be treated as completely new nodes. The default setting for ORDERING is UNORDERED, which means that XQuery results are in the sorted order they existed in the database rather than the sort order specified in the XQuery expression.

The SQL*Plus XQUERY command provides a non-standard but simple method for evaluating XQuery expressions and thus is a complementary tool to XQLPlus. I tend to prefer SQL*Plus XQUERY for XQuery expressions against the Oracle database and prefer XQLPlus for XQuery expressions against non-database resources such as external XML files on the file system.

XQJ: JDBC for XQuery

In recent blog entries, I have posted about using Oracle's XQLPlus command-line XQuery tool. In one of those entries, I pointed out that if XQuery is to XML what SQL is to relational data, then XQLPlus is to XQuery what SQL*Plus is to SQL.

In this blog entry, I intend to demonstrate that we can take these relationships even further and describe the XQuery API for Java (XQJ) as XQuery's JDBC. In other words, XQJ is to XQuery as JDBC is to SQL. Both XQJ and JDBC are Java APIs for accessing data in a standard way. While XQJ is focused on XQuery access of XML data, JDBC is focused on SQL access of relational data. XQJ is currently a work-in-progress as part of Java Specification Request (JSR) 225 ("XQuery API for Java").

As work on JSR 225 precedes, several vendors have made XQJ implementations available. These include Oracle's OJXQI implementation and DataDirect's DataDirect XQuery implementation. DataDirect provides a highly useful XQJ Tutorial (PDF) and DevX provides a brief but useful example of using Oracle's XQJ implementation.

In this blog entry, I use the Oracle XQJ implementation to read an XQuery script file (with an .xql extension in my example) and process it. That processed script file references the source XML document it queries via the fn:doc function. This example does not obtain a connection, which is sometimes a necessary step (such as when querying over XML stored in the database).

The source XML that will be queried for this example is stored in a file called C:\xquery\xmlSource\planets.xml. This sample XML file is shown next.

<?xml version = '1.0'?>
<!-- Note that all data here is not meant to be factual, but is instead intended
     to illustrate XQuery principles.  Also, some of the planets have far too
     many moons to list all of them here, so only select moons are listed in
     those cases. For example, while Jupiter has over 60 moons, only its four
     so-called Galilean moons (the four largest that Galileo could see with the
     available equipment at the time) are listed here. -->
<Planets>
  <Planet name="Mercury"
          minDistanceFromSunMK="46"
   maxDistanceFromSunMK="70"
   class="planet"/>
  <Planet name="Venus"
          minDistanceFromSunMK="108"
   maxDistanceFromSunMK="109"
   class="planet" />
  <Planet name="Earth"
          minDistanceFromSunMK="146"
   maxDistanceFromSunMK="152"
   class="planet">
    <Moons>
      <Moon>The Moon</Moon>
    </Moons>
  </Planet>
  <Planet name="Mars"
          minDistanceFromSunMK="205"
   maxDistanceFromSunMK="249"
   class="planet">
    <Moons>
      <Moon>Phobos</Moon>
      <Moon>Deimos</Moon>
    </Moons>
  </Planet>
  <Planet name="Jupiter"
          minDistanceFromSunMK="741"
   maxDistanceFromSunMK="817"
   class="planet">
    <Moons>
      <Moon>Callisto</Moon>
      <Moon>Europa</Moon>
      <Moon>Ganymede</Moon>
      <Moon>IO</Moon>
    </Moons>
  </Planet>
  <Planet name="Saturn"
          minDistanceFromSunMK="1350"
          maxDistanceFromSunMK="1500"
         class="planet">
    <Moons>
      <Moon>Atlas</Moon>
      <Moon>Calypso</Moon>
      <Moon>Dione</Moon>
      <Moon>Prometheus</Moon>
      <Moon>Pan</Moon>
      <Moon>Pandora</Moon>
      <Moon>Titan</Moon>
    </Moons>
  </Planet>
  <Planet name="Uranus"
          minDistanceFromSunMK="2700"
          maxDistanceFromSunMK="3000"
         class="planet">
    <Moons>
      <Moon>Ariel</Moon>
      <Moon>Cordelia</Moon>
      <Moon>Desdemona</Moon>
      <Moon>Miranda</Moon>
      <Moon>Oberon</Moon>
      <Moon>Ophelia</Moon>
      <Moon>Puck</Moon>
      <Moon>Titania</Moon>
      <Moon>Umbriel</Moon>
    </Moons>
  </Planet>
  <Planet name="Neptune"
          minDistanceFromSunMK="4460"
          maxDistanceFromSunMK="4540"
          class="planet">
    <Moons>
      <Moon>Galatea</Moon>
      <Moon>Larissa</Moon>
      <Moon>Nereid</Moon>
      <Moon>Proteus</Moon>
      <Moon>Triton</Moon>
    </Moons>
  </Planet>
  <Planet name="Pluto"
          minDistanceFromSunMK="7376"
          maxDistanceFromSunMK="4437"
          class="dwarf">
    <Moons>
      <Moon>Charon</Moon>
      <Moon>Hydra</Moon>
      <Moon>Nix</Moon>
    </Moons>
  </Planet>
</Planets>

The XQuery script file to be used to query over the XML source shown above is stored in a different directory and its full path and name are C:\xquery\xqlScripts\extractIAUPlanets.xql. It is important to note that the XQL script and the XML source file are located in different directories because these differences will be reflected in the Java code that uses the XQL script to query the XML source. Here is the short contents of the extractIAUPlanets.xql file:

<IAUnionPlanets>
{for $i in doc("planets.xml")/Planets/Planet
where $i/@class = "planet"
return <Planet>{data($i/@name)}</Planet>}
</IAUnionPlanets>

This short XQuery script file will query over the XML source of planets (which includes Pluto as a planet) and will return only the planets that are still officially considered planets by the International Astronomical Union (IAU). In other words, poor Pluto gets filtered out because it is now a "dwarf planet" instead of a "planet."

Note in the five lines of XQuery script code in the above script, two lines are simply the new XML opening and closing tag for IAUnionPlanets. The other three lines are the more dynamic portion (hence the curly braces) and these only return the names of planets for planets which are of class "planet."

It is significant to note that the fn:doc function references the source XML file (planets.xml) without any path information. The XQuery script will only know where to look for the planets.xml XML source file if we tell the XQuery implementation what the base URI is. In Oracle's XQJ implementation, this is done with a oracle.xquery.Configuration.setBaseURI(String) call. That Configuration can then be passed to the oracle.xquery.XQueryContext.prepareXQuery() call shown in the Java code below. Note that this configuration base URI does not impact the location where the XQL script file will be found, but instead impacts where the XQL script file will look for the source XML file via the fn:doc function.

Here is the Java class (OracleXqjAccess.java) that uses Oracle's XQJ to invoke the XQuery script (C:\xquery\xqlScripts\extractIAUPlanets.xql) shown above on the XML source (C:\xquery\xmlSource\planets.xml) shown above. Here is the code for OracleXqjAccess.java:

package xqueryexamples;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;

import java.util.ArrayList;
import java.util.List;

import oracle.xml.parser.v2.XMLNode;
import oracle.xml.xqxp.datamodel.XMLItem;
import oracle.xml.xqxp.datamodel.XMLSequence;

import oracle.xquery.Configuration;
import oracle.xquery.PreparedXQuery;
import oracle.xquery.XQueryContext;

import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

/**
 * The main purpose of this class is to exercise Oracle's implementation of
 * XQuery API for Java (XQJ) [Java Specification Request 225].
 */
public class OracleXqjAccess
{
   /** XQueryContext. */
   private static XQueryContext context = new XQueryContext();

   /**
    * Default constructor accepting no arguments.
    */
   public OracleXqjAccess ()
   {
   }

   /**
    * Obtain XQueryContext for use in performing XQuery.
    * 
    * @return XQueryContext
    */
   private XQueryContext getXQueryContext()
   {
      if ( context != null )
      {
         return context;
      }
      else
      {
         context = new XQueryContext();
         return context;
      }
   }

   /**
    * Extract text content of non-root first-level XML elements returned from
    * provided XQuery script.
    * 
    * @param aXQueryScript XQuery script to be executed.
    * @param aBaseUri Base URI to be used for any documents looked up within
    *                 the provided aXQueryScript using the fn:doc function.
    * @return List of String associated with child nodes.
    */
   public List<String> extractSingleElementStringsFromFirstLevelElements
      ( final String aXQueryScript,
        final String aBaseUri )
   {
      final String mName = "runExampleXQueryFromFile(String,String)";
      Reader reader = null;
      List <String> childNodesList = new ArrayList<String>();
      try
      {
         final XQueryContext xqueryContext = getXQueryContext();
         final Configuration config = new Configuration();
         reader = new FileReader(aXQueryScript);
         System.out.println( mName + " - XQuery Script File: " + aXQueryScript );
         config.setXQueryOption(Configuration.XQUERY_NORMAL);
         config.setBaseURI(aBaseUri);
         System.out.println( mName + " - Provided Base URI: " + aBaseUri );
         PreparedXQuery preparedXQuery =
            xqueryContext.prepareXQuery(reader, config);
         XMLSequence xrs = preparedXQuery.executeQuery();
         while ( xrs.next() )
         {
            XMLItem xmlItem = xrs.getCurrentItem();
            final XMLNode xmlNode = xmlItem.getNode();
            NodeList childNodes = xmlNode.getChildNodes();
            final int numberChildNodes = childNodes.getLength();
            for ( int i=0; i < numberChildNodes; ++i )
            {
               final Node childNode = childNodes.item(i);
               childNodesList.add( childNode.getTextContent() );
            }
         }
      }
      catch (FileNotFoundException fnfEx)  // use of FileReader
      {
         System.err.println(  "Could not find file " + aXQueryScript + ": "
                            + fnfEx.getMessage() );
      }
      finally
      {
         if ( reader != null )
         {
            try
            {
               reader.close();
            }
            catch (IOException ioEx)
            {
               System.err.println(
                    mName
                  + " - Exception thrown while trying to close Reader."
                  + ioEx.getMessage() );
            }
         }
      }

      return childNodesList;
   }

   /**
    * Provide the full-fledged planets (not dwarf) endorsed by the IAU.
    * 
    * @return List of names of planets endorsed as planets by IAU.
    */
   public List<String> getIAUPlanets()
   {
      final String iauPlanetsScript =
         "C:\\xquery\\xqlScripts\\extractIAUPlanets.xql";
      final String baseURI = "file:///C:/xquery/xmlSource/";
      return extractSingleElementStringsFromFirstLevelElements(
         iauPlanetsScript, baseURI );
   }

   /**
    * Display provided list of strings.
    * 
    * @param aListTitle Title of the list to be displayed.
    * @param aListOfStrings List of Strings to be displayed.
    */
   public static void displayContentsOfList( final String aListTitle,
                                             final List<String> aListOfStrings )
   {
      System.out.println("----- " + aListTitle + " -----");
      for ( final String string : aListOfStrings )
      {
         System.out.println(string);
      }
   }

   /**
    * Main function.
    * 
    * @param aArgs Command-line arguments; none anticipated currently.
    */
   public static void main( final String[] aArgs )
   {
      OracleXqjAccess xqjAccess = new OracleXqjAccess();
      displayContentsOfList( "IAU Endorsed Planets",
                             xqjAccess.getIAUPlanets() );
   }
}

The XQJ-specific and closely related code is highlighted above. There are two System.out statements to display the path and file name of the XQL script file and to display the base URI used implicitly in the XQL script when running it against a source XML file referenced via the fn:doc function.

There are some subtle nuances associated with the code above. As described above, the base URI only describes where the XQL XQuery Script can expect to find the file whose name is passed into the fn:doc function. The code (final String baseURI = "file:///C:/xquery/xmlSource/";) shows the base URI being expressed with all forward slashes rather than the backslashes normally associated with Windows. The Unix-style forward slashes (even in Windows as is this example) and the "file:///" are necessary in the base URI for the Oracle XQuery engine to properly locate the source XML file. Besides ensuring that "file:///" is specified and that all path separators are Unix/Linux style, the other necessary characteristic of the base URI is that it needs to end with a forward slash if being used in a relative sense as in this example. In other words, a slash is NOT automatically put after this base URI before appending the location passed to fn:doc.

While the three rules (specify protocol, always use forward slashes, and always end with a forward slash) are important in specifying the base URI, the file location and name supplied for the XQJ code to find the XQL script file uses more traditional file location syntax: C:\\xquery\\xqlScripts\\extractIAUPlanets.xql (the double backslashes are to handle escaping in Java Strings). In the simpler case of specifying the location of the XQuery script file, one could use Java's System.getProperty("file.separator") to appropriately place file separators for the applicable operating system. For the base URI to the XML source files, on the other hand, the file separators should always be forward slashes regardless of the underlying operating system.

Here is the output from running this Oracle XQJ-powered code to run the prescribed XQuery script over the designated XML file:

runExampleXQueryFromFile(String,String) - XQuery Script File: C:\xquery\xqlScripts\extractIAUPlanets.xql
runExampleXQueryFromFile(String,String) - Provided Base URI: file:///C:/xquery/xmlSource/
----- IAU Endorsed Planets -----
Mercury
Venus
Earth
Mars
Jupiter
Saturn
Uranus
Neptune

The NASA Solar System Exploration site is a useful site for details and facts on the solar system and planets. Another useful site is the "Journey Through the Galaxy" site. My limited experience made the Regular site plenty informative, but people more experienced with galactic knowledge may find the Advanced version more interesting.

Specifying a Document in XQLPlus

In a previous blog entry, I briefly summarized some information regarding XQLPlus. In this blog entry, I focus on how to specify a file in the local file system to XQLPlus via the XQuery function fn:doc.

The example XML for this example is shown below and is saved as a file called C:\xquery\sample1.xml. Here are the contents of C:\xquery\sample1.xml:

<?xml version="1.0"?>
<People>
  <Person id="1" employee="true">
    <LastName>Arthurs</LastName>
    <FirstName>Austin</FirstName>
    <WeeklySalary>1000</WeeklySalary>
  </Person>
  <Person id="2" employee="false">
    <LastName>Baldwin</LastName>
    <FirstName>Barry</FirstName>
    <WeeklySalary>2000</WeeklySalary>
    <Employer>Fictitious and Sons</Employer>
  </Person>
  <Person id="3" employee="true">
    <LastName>Castleton</LastName>
    <FirstName>Carrie</FirstName>
    <WeeklySalary>1500</WeeklySalary>
  </Person>
</People>

One can use the XQuery function fn:doc to access this file in an XQuery expression within XQLPlus, but the path must be carefully specified when it is passed to the fn:doc function to work properly. It is important to specify the protocol for accessing the XML document. In this case, the file is on my local hard drive (C:\), so I will use the file protocol. The other interesting twist is that the path separator following the C: in the document string needs to be a forward slash ("/" in Unix/Linux style) rather than the backward slash style (\) normally associated with Windows directory structures.

The following screen snapshot (click on image to see larger version) demonstrates a very basic XQuery expression designed to print out the same XML body as the source, but with the element NewPeople replacing the element name People.



The first attempt, passing a document path and name without the file protocol and using the normal Windows back slashes, results in an error message: "FODC0005: invalid argument to fn:doc." In the second attempt, the slashes are switched to forward slashes and there is no error message, but only an empty <NewPeople> element tag is created. In the third attempt, the forward slashes are retained and file:/// is prepended to the document's location. This attempt works as we hoped and the XQuery expression returns the new XML. Although not shown in the snapshot above, the same error "FODC0005: invalid argument to fn:doc" is encountered when Windows-style back slashes are used with the file protocol.

It is helpful to be able to run XQuery expressions in XQLPlus against XML files stored on the file system. This is often more practical than loading the XML source to be queried into XQLPlus. As shown above this is easily accomplished as long as the String which is passed to the fn:doc function is built correctly with forward slashes and the file protocol.

An Advantage of Software Development Experience

I have had the good fortune to work with many extremely sharp individuals who are new to software development, but learn rapidly and become significant contributors very quickly. As I have worked with these individuals, I have been extremely impressed with their intellectual abilities and ability to learn. However, I have also been able to recognize some of the advantages that only experience can bring to a developer's overall skillset and some of the lessons that only experienced seems to teach. In this blog entry, I have chosen to focus only on a single such example.

I think that most of us learn by relating new things we hear or read or see to things we already know. For example, most of us were taught how to multiply integers in elementary school by showing us how multiplication was really like adding (which we already knew), but that we were now adding same-sized groups of things instead of adding individual things. We were taught multiplication based on our already assumed knowledge of how to perform addition. Likening multiplication to addition not only helped us to learn multiplication quicker, but also served as a memory device. If we ever forgot the mechanics of multiplication (forgot the multiplication tables we memorized), we could return to our understanding of multiplication related to addition to remember what the product of two numbers is.

Most of us learn about software the same way. I started learning and working with Ruby before transitioning from Java 1.4.2 to Java 5 and the experience helped me to be more comfortable with the Java 5 enhanced for-each loop because I had seen a similar looping syntax in Ruby. Likewise, Ruby on Rails helped to introduce the concept of convention over configuration to me, which made it trivial to understand the Java Persistence API's (JPA's) configuration by exception concept. When I first experimented with Java, my C++ experience was significantly helpful in understanding Java syntax and in learning Java more quickly than I would have without C++ experience. Similarly, my first experiments with C# went much quicker thanks to my Java and C++ experience.

The final example I wanted to cover of this concept has to do with the quantifier symbol notation that is common to XML DTDs, regular expressions, and XQuery.

In regular expressions, the symbols ?, +, and * have special meanings as metacharacters. It turns out that the DTD creators and XQuery creators took advantage of this and gave these same symbols similar meanings in their respective specifications. The table below lists what these three symbols mean in each of these categories.

High-level Technology	Specifically Affected	?	+	*
Regular Expressions	Number of Characters or Groups	Optional	One or more	Any number
XML DTD	Occurrence Indicators (XML Elements)	Optional	One or more	Any number
XQuery	Occurrence Indicators (Type Expressions)	Optional	One of more	Any number

As the table above indicates, the symbol ? means optional (one or zero occurrence) for any character or group it follows in a regular expression and means that same thing for any defined element it follows in a DTD and for any XQuery type expression it follows. Similarly, + means one or more (one to many) for all three (regular expressions, DTD, and XQuery) and * means zero or more (zero to many) for all three areas.

The purpose of this blog has been to both illustrate how software development experience can make a software developer more productive and more adaptable to new technologies and to explicitly demonstrate the the common symbolic notation used for regular expressions, DTDs, and XQuery for specifying quantities of "things" in each area.

There is one downside to all of this, however. While an experienced developer can apply previously learned knowledge and skills to learning a new knowledge or skill, he or she must be cautious about allowing current knowledge to block the ability to think creatively and in new ways. The tendency to always do things the way we've done them before has its downsides and has led to the business lingo favorite: "Think outside of the box."

XQLPlus: SQL*Plus for XQuery

If XQuery is to XML data what SQL (Structured Query Language) is to relational data, then it follows that XQLPlus is to XQuery what SQL*Plus is to SQL.

Chapter 18 of the Oracle XML DB Developer’s Guide 11g Release 1 (“Using Oracle XQuery with XML DB”) states about Oracle’s proprietary convenience SQL function XQUERY: “in effect, this command turns SQL*Plus into an XQuery command-line interpreter.” In other words, the XQUERY function can be used in conjunction with Oracle SQL*Plus to work with XML. However, as documented in Andrej Koelewijn’s IT-eye weblog entry Executing XQuery Statements with XQLPlus, Oracle actually provides a command-line XQuery interpreter tool called XQLPlus. When using XQuery that has little or nothing to do with the database, this XQLPlus tool may be easier to use than using SQL*Plus and the XQUERY function.

This blog entry adds a few more details regarding this handy utility, but I recommend the quick read of the Andrej Koelewijn blog in addition to mine if interested in what XQLPlus has to offer. While there are obviously other references and resources covering XQLPlus, a quick Google search shows that a large percentage of these are simply links and trackbacks to the Andrej Koelewijn blog. Thanks, Andrej, for directing greater focus on this wonderfully useful utility.

The Executing XQuery Statements with XQLPlus blog entry demonstrates running XQLPlus as part of the xquery.jar JAR file included with a JDeveloper installation. This same JAR file can also be found in the Oracle 11g database installation (normal database jlib subdirectory and the sqldeveloper subdirectory) and in the OC4J/Oracle Application Server installation directory. Because the Executing XQuery Statements with XQLPlus blog entry covers using JDeveloper's delivered xquery.jar to access XQLPlus, I'll focus here on using it in the Oracle 11g Database installation instead.

The xquery.jar JAR file is included in the jlib subdirectory of the Oracle Database 11g Release 1 installation. For example, on my machine, it is in the directory C:\app\Dustin\product\11.1.0\db_1\jlib. The XQLPlus class is oracle.xquery.XQLPlus and is available in the xquery.jar JAR file in this directory (you can confirm this with the command jar tvf xquery.jar).

You might be tempted to run the XQLPlus tool directly with the command java -cp xquery.jar oracle.xquery.XQLPlus. Trying this, however, leads to a NoClassDefFoundError for the class oracle/xml/scalable/PageManagerPool. To provide this class's definition, add xmlparserv2.jar to the classpath. This class is not in the same directory as xquery.jar, so its relative directory needs to be specified. When the new command java -cp xquery.jar;..\LIB\xmlparserv2.jar oracle.xquery.XQLPlus is run, you will see the desired XQLPlus prompt as shown in the following image (click on the image to see a larger version of it):



Very simple XQuery expressions can be evaluated with XQLPlus run as shown above. However, for most realistic queries, additional classes will need to be added to the classpath as certain features are encountered by XQLPlus at runtime. For example, the orai18n-collation.jar is required when the class oracle/i18n/text/OraCollator cannot be found.

With the XML Parser Version 2 library and the internationalization library, the command to run XMLPlus is now: java -cp xquery.jar;..\LIB\xmlparserv2.jar;orai18n-collation.jar oracle.xquery.XQLPlus . As you run increasingly complicated XQuery expressions through XQLPlus, it is likely that you'll need to include additional JAR files on the classpath. For instance, xsu12.jar is often needed and that can be found in the same relative database installation directory as xmlparserv2.jar (..\LIB).

The xquery.jar JAR file is also located in the database installation directory in the sqldeveloper area. For example, on my machine, it is located in C:\app\Dustin\product\11.1.0\db_1\sqldeveloper\lib. The xmlparserv2.jar file is in the same directory and the internationalization JAR is in jlib, so XQLPlus can be run from the database's SQL Developer directory with the command java -cp xquery.jar;xmlparserv2.jar;../jlib/orai18n-collation.jar oracle.xquery.XQLPlus.

The Oracle database installation (core and SQL Developer section) and JDeveloper installation are not the only sources of the xquery.jar and its XQLPlus class/utility. This can also be found in OC4J installation directories in the lib subdirectory. For example, it is located in the C:\oc4j_extended_101330\lib directory on my machine. In that installation, the xmlparserv2.jar class is located in the same directory as xquery.jar, so running XQLPlus from this directory boils down to this command: java -cp xquery.jar;xmlparserv2.jar oracle.xquery.XQLPlus.

The Oracle Containers for J2EE Services Guide includes Chapter 8 (Oracle XML Query Service) that talks about OC4J's extensive additional XQuery support above and beyond the XQLPlus Java class.

Because XQLPlus is a Java class run with the normal Java launcher, it is necessary to include dependent JARs on the classpath when running it. This is a little bit of a hassle, so I prefer to include the command as either a CMD script (in DOS) or as a Unix/Linux shell script, alias, or shell function in those environments.

Another useful resource on querying XML with XQLPlus is Querying XML Documents. This resource demonstrates how to run XQLPlus in interactive mode (no file or XQuery expression provided to XQLPlus on the command-line), querying XML from the command-line, and querying XML with XQueryX (XQuery expressions with XML syntax). A resource that is dated but still is useful as an overview of XQLPlus is XQuery Prototype with XQLPlus Readme.

The xquery.jar JAR file with the XQLPlus command-line utility can be found in the installation directories for the Oracle database, for the Oracle database's SQL Developer, for JDeveloper, and for OC4J. The primary difference between these appears to be where dependent JAR files can be found relative to the xquery.jar file. Because these relative locations differ depending on which xquery.jar is used and because the classpath can be several entries long, I recommend capturing the entire call in a script placed in the environment's path that can be executed whenever the XQLPlus tool is required.

No XQuery for Oracle Database Express Edition (XE)

I like to use Oracle's small-footprint database, Oracle Database 10g Express Edition, for many of my development, testing, and publication source code preparation efforts. For example, the Express Edition Database was used in development of source code samples for the Oracle Technology Network (OTN) articles Accessorize Oracle Database with Ruby and Visualize Your Oracle Database Data with JFreeChart.

As documented in the article Oracle Database 10g Express Edition: Not Just for Learners, the main limitations of Oracle Database 10g Express Edition (XE) are often not problematic for development and small business production environments. These limitations include memory, CPU, and disk space limitations as well as a restriction on the number of XE database instances that can run on a single machine and no internal Java Virtual Machine (JVM).

While XE provides general Oracle XML DB support, it does not have its own internal JVM and so cannot support many of the XQuery functions. When trying to use the XMLQuery() SQL function on an XQuery expression, an error message like the following occurs:

ORA-19114: error during parsing the XQuery expression:
PLS-00201: identifier 'SYS.DBMS_XQUERYINT' must be declared

An Oracle online forum entry confirms that XQuery support is not available in Oracle Database 10g XE. This entry explains why lack of support for an internal JVM negatively impacts XE's ability to support XQuery. Another confirmation of lack of internal Java support (and hence lack of XQuery support) in XE is present in a Laurent Schneider blog entry.

Perhaps the best source of information on what Oracle Database XE supports and doesn't support is its licensing documentation. The Oracle Database Express Edition Licensing Information 10g Release 2 (10.2) features Table 2.2 ("Application Development, Language, and Database Features") that indicates that the XE database does NOT support several features related to the information above. Specifically, this table states there is "no Java support in the database," "no Java Server Pages," and "no Java native compilation." Perhaps most importantly for this blog entry, Table 2.2 also says regarding "XML support in the database": "Yes (no XQuery...)". Finally, it is also worth noting that Database XE 10g Release 2 does not support "Database web services" according to this table.

My Planned Presentations for RMOUG Training Days 2008

I received notification that Rocky Mountain Oracle Users Group (RMOUG) has accepted two of my abstracts for presentation at RMOUG's Training Days 2008 conference. The presentations are "Introduction to XQuery: A Multipurpose XML Tool" and "Excel with Apache POI and Oracle Database." Training Days will be February 13-14, 2008, at the Colorado Convention Center.

Presentations from several recent RMOUG Training Days conferences are available at the following links:

• Training Days 2007


• Training Days 2006


• Training Days 2005


• Training Days 2004


• Training Days 2003

---

Other Pages / Blogs on RMOUG Training Days 2008

This portion of this blog entry will be updated regularly as new pages and blogs reference RMOUG Training Days 2008 or RMOUG Training Days in general.

Oracle 2008 Denver Conferences: RMOUG and IOUG Collaborate 2008 (Burleson Consulting)

JL Computer Consultancy Appearances

RMOUG Training Days (W3W3)