Using XML-SQL Utility (XSU), 10 of 26

XML-SQL Utility for Java

The following two XSU classes are useful for the Java API programmer:

oracle.xml.sql.query.OracleXMLQuery: Generates a XML document given a SQL query.
oracle.xml.sql.dml.OracleXMLSave: Puts a XML document into tables and views

Generating XML

The OracleXMLQuery class makes up the XML generation part of the XSU's Java API.

Figure 4-3 illustrates the basic steps in the usage of OracleXMLQuery.

Perform these steps when generating XML:

First create a connection
Create an OracleXMLQuery instance by supplying a SQL string or a ResultSet object
Get the result as either a DOM tree or as an XML string

Figure 4-3 Generating XML With XML-SQL Utility for Java: Basic Steps

The following example, shows how a simple XML document can be generated.

XSU: Basic Generation of XML From SQL Queries

These examples illustrate how using the XSU you can get a XML document in its DOM or string representation given a SQL query. See Figure 4-4.

Figure 4-4 Generating XML With the XML-SQL Utility: Process and Options

XSU Example 1: Generating a String From emp table

The first step before getting the XML is to create a connection to the database. The connection can be obtained by supplying the JDBC connect string. You have to first register the Oracle JDBC class and then create the connection.

// import the Oracle driver..
import oracle.jdbc.driver.*;

// Load the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());     
// Create the connection.
Connection conn =   
 DriverManager.getConnection("jdbc:oracle:oci8:@","scott","tiger");

Here, the connection is done using the OCI8 JDBC driver. You can connect to the scott schema supplying the password tiger. It connects to the current database (identified by the ORA_SID environment variable). You can also use the JDBC thin driver to connect to the database. The thin driver is written in pure Java and can be called from within applets or any other Java program.

See Also::
Oracle8i Java Developer's Guide for more details on this

Connecting With the Thin Driver

Here's an example of connecting using the thin driver.

// Create the connection.
Connection conn =  
      DriverManager.getConnection("jdbc:oracle:thin:@dlsun489:1521:ORCL",
                                  "scott","tiger");

The thin driver requires the specification of the host name (dlsun489), port number (1521) and the oracle SID (ORCL) which identifies a specific Oracle instance on the machine.

No Connection Needed When Run In the Server

In the case of writing server side Java code, i.e., code that will run inside the server, you need not establish a connection using a username and password, since the server-side internal driver runs within a default session. You are already "connected". In that case you call the defaultConnection() on the oracle.jdbc.driver.OracleDriver() class to get the current connection.

import oracle.jdbc.driver.*;

// Load the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());     
Connection conn =  new oracle.jdbc.driver.OracleDriver ().defaultConnection ();

The rest of the notes will either assume the OCI8 connection from the client or that you already have a connection object created. Use the appropriate connection creation based on your needs.

Creating an OracleXMLQuery Class Instance

Once you have registered your connection, create an OracleXMLQuery class instance by supplying a SQL query to execute,

// import the query class in to your class
import oracle.xml.sql.query.OracleXMLQuery;

OracleXMLQuery qry = new OracleXMLQuery (conn, "select * from emp");

You are now ready to use the query class.

XML String Output:

You can get a XML string for the result by:

String xmlString = qry.getXMLString();

DOM Object Output:

If, instead of a string, you wanted a DOM object instead, you can simply ask for a DOM output,

org.w3c.DOM.Document domDoc = qry.getXMLDOM();

and use the DOM traversals.

Here's a complete listing of the program to extract the XML string. This program gets the string and prints it out to the standard output.

Import oracle.jdbc.driver.*;
import oracle.xml.sql.query.OracleXMLQuery;
import java.lang.*;
import java.sql.*;

// class to test the String generation!
class testXMLSQL {

   public static void main(String[] argv)
   {

     try{
      // create the connection
      Connection conn  = getConnection("scott","tiger");

      // Create the query class.
      OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from emp");

      // Get the XML string
      String str = qry.getXMLString();

      // Print the XML output
      System.out.println(" The XML output is:\n"+str);
      // Always close the query to get rid of any resources..
     qry.close();
     }catch(SQLException e){
      System.out.println(e.toString());
     }
   }

   // Get the connection given the user name and password..!
   private static Connection getConnection(String username, String password)
     throws SQLException
   {
      // register the JDBC driver..
       DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());


      // Create the connection using the OCI8 driver
       Connection conn =
        DriverManager.getConnection("jdbc:oracle:oci8:@",username,password);

      return conn;
   }
}

How to Run This Program?

To run this program, carry out the following:

Store this in a file called testXMLSQL.java
Compile it using javac-the Java compiler
Eexecute it by specifying "java testXMLSQL"

You must have the CLASSPATH pointing to this directory for the java executable to find the class. Alternatively use various visual Java tools including Oracle's JDeveloper to compile and run this program.

When run, this program prints out the XML file to the screen.

XSU Example 2: Generating DOM From emp table (Java)

A DOM (Document Object Model) is a standard defined by the W3C committee which represents an XML document in a parsed-tree like form. Each XML entity becomes a DOM node. Thus XML elements, attributes become DOM nodes and their children become child nodes.

To generate a DOM tree from the XML generated by the utility, it is efficient to directly ask for a DOM Document from the utility, as it saves the overhead of creating a string representation of the document and then parse it to generate the DOM tree.

XSU calls the parser to directly construct the DOM tree from the data values. The example is shown below to get the DOM tree. The example "walks" through the DOM tree and prints all the nodes one by one.

  
import org.w3c.dom.*;
import oracle.xml.parser.v2.*;
import java.sql.*;
import oracle.xml.sql.query.OracleXMLQuery;
import java.io.*;

 class domTest{

   public static void main(String[] argv)
   {
      try{
      // create the connection
      Connection conn  = getConnection("scott","tiger");

      // Create the query class.
      OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from emp");

      // Get the XML DOM object. The actual type is the Oracle Parser's DOM
      // representation. (XMLDocument)
      XMLDocument domDoc = (XMLDocument)qry.getXMLDOM();

      // Print the XML output directly from the DOM
      domDoc.print(System.out);

      // If you instead want to print it to a string buffer you can do 
this..!
      StringWriter s = new StringWriter(10000);
      domDoc.print(new PrintWriter(s));
      System.out.println(" The string version ---> "+s.toString());

      qry.close(); // You should always close the query!!
      }catch(Exception e){
        System.out.println(e.toString());
      }
    }

    // Get the connection given the user name and password..!
    private static Connection getConnection(String user, String passwd)
      throws SQLException
    {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      Connection conn =
          DriverManager.getConnection("jdbc:oracle:oci8:@",user,passwd);
     return conn;
   }
}