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
XQUERYfunction 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.jarJAR file included with a JDeveloper installation. This same JAR file can also be found in the Oracle 11g database installation (normal database
jlibsubdirectory and the
sqldevelopersubdirectory) and in the OC4J/Oracle Application Server installation directory. Because the Executing XQuery Statements with XQLPlus blog entry covers using JDeveloper's delivered
xquery.jarto access XQLPlus, I'll focus here on using it in the Oracle 11g Database installation instead.
xquery.jarJAR file is included in the
jlibsubdirectory 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.XQLPlusand is available in the
xquery.jarJAR 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.jarto 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.XQLPlusis 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
XQLPlusrun 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.jaris required when the class
oracle/i18n/text/OraCollatorcannot 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.jaris often needed and that can be found in the same relative database installation directory as
xquery.jarJAR file is also located in the database installation directory in the
sqldeveloperarea. For example, on my machine, it is located in
xmlparserv2.jarfile 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
XQLPlusclass/utility. This can also be found in OC4J installation directories in the
libsubdirectory. For example, it is located in the
C:\oc4j_extended_101330\libdirectory on my machine. In that installation, the
xmlparserv2.jarclass 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
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.
xquery.jarJAR 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.jarfile. Because these relative locations differ depending on which
xquery.jaris 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.