Introduction to OJXQI

What is OJXQI?

Here is a simple example of an XQuery usage with OJXQI. This reads the XQuery query from the file exmpl1.xql and then executes the query and prints out the result DOM nodes.

   XQueryContext ctx = new XQueryContext();

   try
   {
     // create a string from the file
     Reader strm = new FileReader("exmpl1.xql");

     // prepare the query
     PreparedXQuery xq = ctx.prepareXQuery(strm);

     // get a resultset
     XQueryResultSet rset = xq.executeQuery();

     while (rset.next())
     {
       XMLNode node = rset.getNode();  // get result nodes
       System.out.println(" NODE "+ node.getNodeName());
       node.print(System.out);
     }
   }
   catch (Exception e)
   {
      // do something..
   }

• To use the OJXQI API, you would have to first create a context. This context is used to keep prepare multiple XQuery statements later. The context keeps track of metadata such as the Connection to use to connect to the database (if necessary) etc.. In this example, we simply use a default constructor.
• Next, we create a FileReader stream object to read the XQuery query (We could use a StringReader if the Query was inlined).
• We prepare the XQuery statement, by calling the prepareXQuery() call. This parses and compiles the XQuery into internal structures.
• After this, we can call the executeQuery() statement to execute the query. Note that we should have bound values to any bind variable by this time, if our query included bind variables described later. 
• Executing the query returns a result set which can be used to fetch the data. The return in this example is a DOMNode() which we print out.

Steps for using OJXQI

• Create a context by using the default constructor or passing in SQL connection information
• Prepare a XQuery or XQueryX statement by calling prepareXQuery or prepareXQueryX() respectively. This compiles the XQuery statement and returns a PreparedXQuery object.
• Bind any values to the PreparedXQuery object.
• Execute the preparedXQuery object. This would return a XqueryResultSet object.
• Iterate over the result set object (using the next() function) and use the getNode() calls on the XqueryResultSet to get the return values. The iterator iterates over each result from the top level FLWR expression in the XQuery and returns a result. For example, the XQuery,
for $i in document("emp.xml")/PurchaseOrder return $i
would return a list of purchase order nodes. The XQueryResultSet would iterate over these nodes and return a purchaseorder node for each iteration.

Advantages of OJXQI

• Similar to JDBC hence easy to understand.
• Support for preparedStatements makes it more scalable, since all queries can be precompiled and used multiple times without having to parse and re-compile.
• Support for bind variables make it more scalable, when the queries change only with constants. The same query can now be used when the only difference is a constant value.
• The bind variable allows you to bind DOM objects directly into the XQuery. So you can query transient DOM instances instead of XML stored in files.

Oracle enhancements in OJXQI

• Support for SQL queries to be embedded inside XQuery
• Support for bind variables, so as to not re-execute the XQuery for every constant changes
• Support for XQueryX - an XML representation of the XQuery language - for ease of mechanical generation and translation of XQuery.

Support for SQL queries

• For example, the following XQuery gets the results of the employee table in XML and returns the employee element in a different format -

FOR $i IN sqlquery("select * from scott.emp")/ROW
RETURN
  <EMP empno="{$i/EMPNO}">
    $i/ENAME,
    $i/SALARY
  </EMP>
 
• The following XQuery gets the result of the SQL query, and joins that with a local file source. In this case, we get the results from the employee table in SCOTT's schema in the database and join that with the department values stored in a local dept.xml file.

FOR $i IN sqlquery("select * from scott.emp")/ROW,
    $j IN document("dept.xml")
WHERE $j/deptno = $i/DEPTNO
RETURN
  <EMP empno="{$i/EMPNO}" deptno="{$j/deptno}"/>

  // get the connection (for example, using the thick JDBC Driver)
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  Connection conn =
      DriverManager.getConnection("jdbc:oracle:@","scott","tiger");

  // create a context using that connection..
  XQueryContext ctx = new XQueryContext(conn);

  // create a string from the file
  Reader strm = new StringReader(
     "FOR $i IN sqlquery(\"select * from scott.emp\")/ROW "+
     " RETURN <EMP empno=\"{$i/EMPNO}\">$i/ENAME,$i/SALARY</EMP>");

  // prepare the query
  PreparedXQuery xq = ctx.prepareXQuery(strm);

  // get a resultset
  XQueryResultSet rset = xq.executeQuery();

  while (rset.next())
  {
    XMLNode node = rset.getNode();  // get result nodes
    System.out.println(" NODE "+ node.getNodeName());
    node.print(System.out);
  }

Support for URLs in document function

The XQueryContext also supports a constructor that takes in a baseUrl. This is useful for changing the location from which the document function picks up the files without having to modify the XQuery query itself.

Support for bind variables

Example for binding DOM nodes

import java.sql.*;
import oracle.xml.parser.v2.*;
import oracle.xquery.*;
import java.io.*;
import java.util.*;
import oracle.xquery.exec.*;

public class testBindDOM
{
  public static void main(String argv[])
  {
    XQueryContext ctx = new XQueryContext();

    try
    {
      // create a string from the file
      PreparedXQuery xq = ctx.prepareXQuery(
                " for $iN bind('1')/bib/book            "+
                " WHERE $b/publisher = 'Addison-Wesley' "+
                "   AND $b/@year > 1991                 " +
                "    RETURN  <book year={ $b/@year }>   "+
                "            { $b/title }  </book>      ");

      DOMParser prs = new DOMParser();
      InputStream is = Utils.getStream("book.xml");
      prs.parse(is);

      XMLDocument doc = prs.getDocument();
      xq.setNode((new Integer(i)).toString(), doc);

      System.out.println(" Executing Query ");
      XQueryResultSet rset = xq.executeQuery();
      int pos = 0;

      while (rset.next())
     {
       System.out.println(" ---- Fetching Node["+pos+"] ---- ");
       pos ++;

       XMLNode node = rset.getNode();
       System.out.println(" NODE "+ node.getNodeName());
       node.print(System.out);
     }
    }
    catch (Exception e)
    {
        System.out.println(" got exception "+e.getMessage());
    }
  }
}

How are bind values determined?

Example for binding Strings

import java.sql.*;
import oracle.xml.parser.v2.*;
import oracle.xquery.*;
import java.io.*;
import java.util.*;
import oracle.xquery.exec.*;

public class testBindString
{
  public static void main(String argv[])
  {
    XQueryContext ctx = new XQueryContext();

    try
    {
      // create a string from the file
      PreparedXQuery xq = ctx.prepareXQuery(
                " for $iN document(bind(\"filename\"))         "+
                " WHERE $b/publisher = 'Addison-Wesley' "+
                "   AND $b/@year > 1991                 " +
                "    RETURN  <book year={ $b/@year }>   "+
                "            { $b/title }  </book>      ");

 
      // Loop over the input arguments
      for (int i = 0; i < argv.length; i++)
      {
        System.out.println(" Binding value "+i);
        xq.setString("filename", argv[i]);

        System.out.println(" Executing Query... ");
        XQueryResultSet rset = xq.executeQuery();
        int pos = 0;

        while (rset.next())
        {
          System.out.println(" ---- Fetching Node["+pos+"] ---- ");
          pos ++;

          XMLNode node = rset.getNode();
          System.out.println(" NODE "+ node.getNodeName());
          node.print(System.out);
        }
      }
    }
    catch (Exception e)
    {
        System.out.println(" got exception "+e.getMessage());
    }
  }
}

you can execute this program by recompiling it and then supplying the list of filenames,
e.g.

  java testBindString exmpl1.xml exmpl2.xml exmpl3.xml