IT-eye

Oracle’s xquery implementation contains a sqlplus like tool which you can use to run simple xquery statements. You can find the xquery implementation in %JDEV_HOME%/lib. To run it, use the following statement:

E:oraclejdev1111r2lib>java -classpath xquery.jar;xmlparserv2.jar;xsu12.jar;..jdbclibojdbc14.jar;..jliborai18n-collation.jar
oracle.xquery.XQLPlus

XQuery Command Line Tool
Version 1.0
Enter XQuery statements followed by /;

XQL> help

XQuery Command Line Tool
Enter XQuery statements followed by /;
To execute queries in a file from the XQL prompt, enter @ followed by the <filename>
To execute queries in a file from the shell prompt, enter java XQLPlus </filename><filename>
Set command helps set environment variables:
set sqlconn default <jdbc -connect-string>
set sqlconn <name> <jdbc -connect-string>
set echo   (ON | OFF)
set timing (ON | OFF)
set var <variable name> <value>
set initialctx <filename>

A simple xquery example:

XQL> for $i in <doc><row>text1</row><row>text2</row><row>text3</row></doc>//row return $i
  2  /;

Result
----------------------------------------------------
<row>text1</row>

<row>text2</row>

<row>text3</row>

You can use xquery to query data in a database table. First you need to specify the database connection using set sqlconn. when you’ve set the database you can use ora:view to query data from a table:

XQL> set sqlconn default jdbc:oracle:thin:jat/jat@xesoa1:1521:xe
XQL> for $i in ora:view('BOOKS') return $i
  2  /;

Result
----------------------------------------------------
<row>
   <id>1</id>
   <title>Processing XML with Java: A Guide to SAX, DOM, JDOM, JAXP, and TrAX</title>
   <author_id>1</author_id>
</row>

<row>
   <id>2</id>
   <title>Java &amp; XML, 2nd Edition: Solutions to Real-World Problems</title>
   <author_id>2</author_id>
</row>

<row>
   <id>3</id>
   <title>Java, XML, and the JAXP</title>
   <author_id>3</author_id>
</row>

You can also use xquery to query data from xml documents:

XQL> for $i in doc('file:///E:/projects/xquery-demo/xquerydemo-1/src/nl/iteye/xquerydemo1/books.xml')//book where $i/title/text() =
'Xml primer' return $i
  2  /;

Result
----------------------------------------------------
<book category="WEB">
   <title lang="en">Xml primer</title>
   <author>Erik T. Ray</author>
   <year>2003</year>
   <price>39.95</price>
</book>

By default xquery statements are executed by the client side xquery implementation. You can also execute your statements in a database server:

XQL> set server on
  Server Side Execution
XQL> set sqlconn default jdbc:oracle:thin:hr/hr@ora11:1521:orcl
XQL> for $i in <doc><row>1</row></doc> return $i
  2  /;

Result
----------------------------------------------------
<doc><row>1</row></doc>
XQL>

Of course, you can also use sqlplus to execute xquery statements on an Oracle database server.

Btw, you should be able to specify variable values using “set var”, but i haven’t been able to get that to work. And after looking at the source code, i don’t think that’s implemented, at least for client side xquery statements.