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.