XML-SQL Utility (XSU)

What is the XML-SQL Utility (XSU)

XML has rapidly become the format for data interchange; at the same time, a substantial amount of business data resides in object-relational databases. It is therefore necessary to have the ability to transform this "relational" data to XML.

XML-SQL Utility (XSU) enables one to do just this:

For example, on the XML generation side, specified the query "select * from emp", the XSU will query the database and return the query results in the form of the following XML document:

<?xml version='1.0'?>
<ROWSET>
	 	 <ROW num="1">
 	 	  <EMPNO>7369</EMPNO>
    <ENAME>Smith</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>12/17/1980 0:0:0</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </ROW>
  <!-- additional rows ... -->
</ROWSET>

Going the other way, given the XML document above, the XSU can extract the data from it and insert it into the scott.emp table.

XML-SQL Utility's functionality is accessable through a Java API, a PL/SQL API, or a java command line front end.

XSU Features at a Glance

XSU has the following features:

XSU New Features at a Glance

XSU has the following new features, offered with Oracle9i:

XSU Dependencies and Installation

Dependencies

XML-SQL Utility needs the following components in order to function:

Installing the XSU using the Oracle Installer

XML-SQL Utility (XSU) is packaged with Oracle8i (8.1.7 and later) and Oracle9i.

The XML-SQL Utility is made up of three files:

By default the Oracle9i installer installs XSU on your hard drive (in the locations specified above) as well as loads it into the database.

Now, if during initial installation you choose not to install the XSU, you can still install it later, but the installation gets a bit trickier... You start by installing the XSU and its dependent components on your system. You can accomplish this using the oracle installer. Next you perform the following steps:

  1. If you haven't yet loaded the xmlparser for java into the database, go to $ORACLE_HOME/xdk/lib. Here you will find xmlparserv2.jar which you will need to load into the database. For how to accomplish this see "Loading JAVA Classes" in the Oracle8i Java Stored Procedures Developer's Guide

  2. Next go to $ORACLE_HOME/admin and execute the catxsu.sql script

Installing the XSU downloaded from OTN

Download the correct XSU distribution archive from the Oracle Technology Network web-site (http://otn.oracle.com). Expand the downloaded archive. Depending on the usage scenario, perform the following install tasks:

XML-SQL Utility and the Bigger Picture

XML-SQL Utility (XSU) is written in Java,thus can live in any tier that supports Java.

XML-SQL Utility in the Database

The Java classes which make up XSU can be loaded into a java enabled Oracle8i or later; furthermore, the XSU contains a PL/SQL wrapper which publishes the XSU's Java API to PL/SQL creating a PL/SQL API. This way one can write new java applications which run inside the database and which directly access the XSU's Java API; one can write PL/SQL applications which access XSU through its PL/SQL API; or one can access the XSU's functionality directly through SQL. Note that to load and run Java code inside the database you need a java enabled Oracle8i or later Server.

Figure 4-1 shows the typical architecture for such a system. XML generated from XSU running inside the database can be placed in advanced queues in the database to be queued to other systems or clients. The XML can be used from within stored procedures inside the database or shipped outside via web servers or application servers.


Note:

In Figure 4-1 all lines are bi-directional. Since XSU can generate as well as save data, data can come from various sources to XSU running inside the database and can be put back in the appropriate database tables. 


Figure 4-1 Running XML-SQL Utility in the Database

Text description of adxml018.gif follows

XML-SQL Utility in the Middle Tier

Your application architecture may force the use of an 'application server' in the middle tier that is separate from the database. This application tier could be an Oracle database, an Oracle 'application server', or a third party application server that supports Java programs.

You may want to generate XML in the middle tier from SQL queries or ResultSets for various reasons. For example, to integrate different JDBC data sources in the middle tier. In this case you would install the XSU in your middle tier and have your Java programs make use of the XSU through its Java API.

Figure 4-2, shows how a typical architecture for running XSU in a middle tier. In the middle tier, data from JDBC sources is converted by XSU into XML and then sent to web servers or other systems. Again, the whole process is bi-directional and the data can be put back into the JDBC sources (database tables or views) using XSU. If an Oracle database itself is used as the application server, then you can also use the PL/SQL front-end instead of Java.

Figure 4-2 Running XML-SQL Utility in the MIddle Tier

Text description of adxml019.gif follows

XML-SQL Utility in a Web Server

XSU can live in the Web Server, as long as the web server supports Java servlets. This way one can write Java servlets which use the XSU to accomplish their task.

XSQL servlet does just this. XSQL servlet is a standard servlet provided by Oracle which is built on top of the XSU providing the user with a template like interface to the XSU's functionality. If XML processing in the web server is your goal, you should probably use the XSQL servlet, as it will spare you from the intricate servlet programming.

Text description of adxml020.gif follows

XML-SQL Utility In The Client Tier

XML-SQL Utility can be also installed on a client system. Here one can write Java programs which can use the XSU. Or, one can directly use the XSU through its command-line-front-end.

SQL-XML Mapping Primer

As described earlier, the XML-SQL Utility transforms data retreived from object-relational database tables or views into XML. The XSU can also extract data from an XML document, and using a set mapping, insert the data into the appropriate columns/attribute of a table or a view. This section desribes this canonical mapping/transformation used to go from SQL to XML or vice versa.

Default SQL to XML Mapping

Consider the table emp as follows:

CREATE TABLE emp 
(
   EMPNO NUMBER, 
   ENAME VARCHAR2(20),
   JOB VARCHAR2(20),
   MGR  NUMBER,
   HIREDATE DATE,
   SAL NUMBER,
   DEPTNO NUMBER
);

Specified the query "select * from emp", the XSU would generate the following XML document:

 <?xml version='1.0'?>
<ROWSET>
	<ROW num="1">
	  <EMPNO>7369</EMPNO>
    <ENAME>Smith</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>12/17/1980 0:0:0</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </ROW>
  <!-- additional rows ... -->
</ROWSET>

In the generated XML, the rows returned by the SQL query are enclosed in a ROWSET tag to make the <ROWSET> element, which is also the root element of the generated XML document.

The <ROWSET> element contains one or more <ROW> elements. Each of these <ROW> elements contains the data from one of the returned db rows.

Specifically, each <ROW> element contains one or more elements whose names and content are those of the database columns specified in the select list of the SQL query. And finally, these elements corresponding to database columns contain the data from the columns.

Next we describe this mapping but against an object-relational schema like the following:

The AddressType is an object type whose attributes are all scalar types...

CREATE TYPE AddressType AS OBJECT (
   STREET VARCHAR2(20),
   CITY   VARCHAR2(20),
   STATE  CHAR(2),
   ZIP    VARCHAR2(10)
);
/

An EmplyeeType is also an object type but whose EMPADDR attribute is of an object type itself, specifically AddressType...

CREATE TYPE EmployeeType AS OBJECT
(
  EMPNO NUMBER,
  ENAME VARCHAR2(20),
  SALARY NUMBER,
  EMPADDR AddressType
);
/

EmployeeListType is a collection type whose elements are of EmployeeType object type...

CREATE TYPE EmployeeListType AS TABLE OF EmployeeType;
/

Finally, dept is a table with, among other things, an object type column and a collection type column -- AddressType and EmployeeListType respectively.

CREATE TABLE dept 
(
  DEPTNO NUMBER,
  DEPTNAME VARCHAR2(20),
  DEPTADDR AddressType,
  EMPLIST  EmployeeListType
);

Assuming that valid values are stored in the dept table, given the query "select * from dept", the XSU will generate the following XML document:

<?xml version='1.0'?>
<ROWSET>
	<ROW num="1">
 	  <DEPTNO>100</DEPTNO>
    <DEPTNAME>Sports</DEPTNAME>
    <DEPTADDR>
      <STREET>100 Redwood Shores Pkwy</STREET>
      <CITY>Redwood Shores</CITY>
      <STATE>CA</STATE>
      <ZIP>94065</ZIP>
    </DEPTADDR>
    <EMPLIST>
      <EMPLIST_ITEM num="1">
         <EMPNO>7369</EMPNO>
         <ENAME>John</ENAME>
         <SALARY>10000</SALARY>
         <EMPADDR>
           <STREET>300 Embarcadero</STREET>
           <CITY>Palo Alto</CITY>
           <STATE>CA</STATE>
           <ZIP>94056</ZIP>
         </EMPADDR>
      </EMPLIST_ITEM>
       <!-- additional employee types within the employee list -->
    </EMPLIST>
  </ROW>
  <!-- additional rows ... -->
</ROWSET>

As in the last example, the mapping is canonical -- <ROWSET> contains <ROW>'s which contain elements corresponding to columns. And as before, the elements corresponding to scalar type columns simply contain the data from the column.

Things get a touch more complex with elements corresponding to a complex type column. For example, the <DEPTADDR> corresponds to the DEPTADDR column which is of an object type ADDRESS; consequently, the <DEPTADDR> contains sub-elements corresponding to the attributes specified in the type ADDRESS. In turn, these sub-elements can contain the actual data, or they can contain sub-elements of their own, again depending if the attribute they correspond to is of a simple type or a complex type.

When dealing with elements corresponding to database collections, things are yet different. Specifically, the <EMPLIST> element corresponds to the EMPLIST column which is of a EmployeeListType collection type; consequently, the <EMPLIST> element contains a list of <EMPLIST_ITEM> elements each corresponding to one of the elements of the collection.

Other observations to make about the above mapping are:

Customizing the Generated XML

Often, one needs to generate XML which has a specific structure. Since the desired structure might very well differ from the default structure of the generated XML document, it is extremely desirable to have some flexibility in this process.

The ways in which one can go about customizing the structure of a generated XML document fall in one of three categories.

Source Customization

This category incompases customizations done by altering the query or the database schema. Among the simplest and the most powerful source customizations are:

Mapping Customization

The XML-SQL Utilty allows one to tweak the mapping the XSU uses to transform SQL data into XML. Following is the list of possible tweaks:

Post-Generation Customization

Finally, if the desired customization can not be achieved with the methods described above, one can write an XSL Transformation and register it with the XSU. While there is an XSLT registered with the XSU, the XSU will apply the XSLT to any XML it generates

Default XML to SQL Mapping

XML to SQL mapping is pretty much just the reverse of the SQL to XML mapping (see: Default SQL to XML Mapping). Following are differences to consider:

If the XML document doesn't perfectly map into the target database schema, there are three things you can do about it:

How XML-SQL Utility Works

This section provides you with insight into the way XSU works.

Select

XSU generation is quite simple. SQL queries are executed and the resultset is retreived from the database. Metadata about the resultset is aquired and analized. Then using the mapping described in Default SQL to XML Mapping, the SQL resultset is processed and converted into an XML Document.

Insert

To insert the contents of an XML document into a particular table/view the XSU first retreives the metadata about the target table/view. Based on the metadata the XSU generates a SQL insert statement. Next XSU extracts the data out of the XML document and binds it to the appropriate columns/attributes. Finally the statement is executed.

For example, assume that the target table is dept and the XML document is the one generated from dept (see: Default SQL to XML Mapping). XSU would generate the following insert statement.

INSERT INTO Dept (DEPTNO, DEPTNAME, DEPTADDR, EMPLIST) VALUES (?,?,?,?)

Next, the XSU would parse the XML document, and for each record, it would bind the appropriate values to the appropriate columns/attributes and execute the statement:

DEPTNO <- 100
DEPTNAME <- SPORTS
DEPTADDR <- AddressType('100 Redwood Shores Pkwy','Redwood Shores',
                        'CA','94065')

EMPLIST <- EmployeeListType(EmployeeType(7369,'John',100000,
            AddressType('300 Embarcadero','Palo Alto','CA','94056'),...)

The insert processing can be optimized to insert in batches, and commit in batches. More detail on batching can be found in the section on "Insert Processing".

Update

Updates and deletes differ from insert in that they can affect more than one row in the database table. In the case of insert, each ROW element of the XML document can affect at most one row in the table, provided that there are no triggers or constraints on the table. However, in the case of updates and deletes, the XML element might match more than one row if the matching columns are not key columns in the table.

In the case of updates, you are expected to provide a list of key columns which the XSU will use to identify the row to update. For example, to update the DEPTNAME to SportsDept instead of Sports, you can have an XML document such as,

<ROWSET>
	<ROW num="1">
 	  <DEPTNO>100</DEPTNO>
    <DEPTNAME>SportsDept</DEPTNAME>
  </ROW>
</ROWSET>

and supply the DEPTNO as the key column. This would fire off the following update statement:

UPDATE DEPT SET DEPTNAME = ? WHERE DEPTNO = ? 

and bind the values,

DEPTNO <- 100
DEPTNAME <- SportsDept

In the update case, you can also choose to update only a set of columns and not all the elements present in the XML document. See also, "Update Processing" .

Delete

In the case of deletes, you can choose to give a set of key columns for the delete to identify the rows. If the set of key columns are not given, then the delete statement will try to match all the columns given in the document. Given a document such as:

<ROWSET>
	<ROW num="1">
 	  <DEPTNO>100</DEPTNO>
    <DEPTNAME>Sports</DEPTNAME>
    <DEPTADDR>
      <STREET>100 Redwood Shores Pkwy</STREET>
      <CITY>Redwood Shores</CITY>
      <STATE>CA</STATE>
      <ZIP>94065</ZIP>
    </DEPTADDR>
  </ROW>
  <!-- additional rows ... -->
</ROWSET>

to delete, the utility will fire off a delete statement (one per ROW element) which would look like the following:

DELETE FROM Dept WHERE DEPTNO = ? AND DEPTNAME = ? AND DEPTADDR = ?
binding,
DEPTNO <- 100
DEPTNAME <- Sports
DEPTADDR <-  AddressType('100 Redwood Shores Pkwy','Redwood 
City','CA','94065')

See also, "Delete Processing" .

Using the XSU Command Line Front End

XSU comes with a simple command line front-end which gives user a quick access to XSU's XML generation and XML insertion functionality. At this point, the XSU front end does not publish the update and delete functionalities of the XSU.

The command line options are provided through the java class OracleXML. Invoke it by calling:

java OracleXML

The above call will result the front-end usage information to be printed.

To be able to run the XSU front-end, you first need to specify where is the executable located. To do this add the XSU java library (xsu12.jar or xsu111.jar) to your CLASSPATH.

Now, since the XSU has a dependency on the Oracle XML Parser and the JDBC drivers, for the XSU to run, you need to make the location of these components also known. To do this, your CLASSPATH needs to include the locations of the Oracle XML Parser java library (xmlparserv2.jar) and the JDBC library (classes12.jar if using xsu12.jar or classes111.jar if using xsu111.jar).

Generating XML using XSU's Front-End

To use the generation capabilities, call XSU with the getXML parameter. For example, to generate an XML document by querying the emp table under scott schema,

java OracleXML getXML -user "scott/tiger" "select * from emp"


This performs the following tasks:

getXML supports a wide range of options which are explained in the following section.

OracleXML - getXML Options

Table 4-1 lists the OracleXML -getXML options:

Table 4-1 OracleXML -getXML Options
-getXML Option  Description 

-user "<username>/<password>" 

Used to specify the user name and password to connect to the database. If this is not specified, the user defaults to "scott/tiger". Note that he connect string is also being specified, the user name and password can be specified as part of the connect string. 

-conn "<JDBC_connect_string>" 

Used to specify the JDBC database connect string. By default the connect string is: "jdbc:oracle:oci8:@"): 

-withDTD 

Instructs the XSU to generate the DTD along with the XML document. 

-rowsetTag "<tag_name>" 

Used to specify rowset tag (i.e. the tag that encloses all the XML elements corresponding to the records returned by the query) The default rowset tag is ROWSET. Specifying an empty string for the rowset tells the xsu to completely omit the rowset element.  

-rowTag "<tag_name>" 

Used to specify the row tag (the tag used to enclose the data coresponding to a database row). The default row tag is ROW. Specifying an empty string for the row tag tells the xsu to completely omit the row tag. 

-rowIdAttr "<row_id-attribute-name>" 

Used to name the attribute of the ROW element keeping track of the cardinality of the rows. By default this attribute is called "num". Specifying an empty string (i.e. "") as the row id attribute will tell the XSU to omit the attribute. 

-rowIdColumn "<row Id column name>" 

Used to specify that the value of one of the scalar columns from the query should be used as the value of the row id attribute. 

-collectionIdAttr "<collection id attribute name>" 

Used to name the attribute of a XML list element keeping track of the cardinality of the elements of the list (note: the generated XML lists correspond to either a cursor query, or collection). Specifying an empty string (i.e. "") as the row id attribute will tell the XSU to omit the attribute.. 

-useNullAttrId 

Used to tell the XSU to use the attribute "NULL (TRUE/FALSE)" to indicate the nullness of an element. 

-styleSheet "<stylesheet URI>" 

Used to specify the stylesheet in the XML PI (Processing Instruction). 

-stylesheetType "<stylesheet type>" 

Used to specify the stylesheet type in the XML PI (Processing Instruction). 

-errorTag "<error tag name>" 

Used to specify the error tag -- the tag to enclose error messages which are formated into XML. 

-raiseNoRowsException 

Used to tell the XSU to raise an exception if no rows are returned. 

-maxRows "<maximum number of rows>" 

Used to specify the maximum number of rows to be retreived and converted to XML. 

-skipRows "<number of rows to skip>" 

Used to specify the number of rows to be skipped. 

-encoding "<encoding name>" 

Used to specify the characterset encoding of the generated XML. 

-dateFormat "<date format>" 

Used to specify the date format for the date values in the XML document. 

-fileName "<SQL query fileName>" | <sql query> 

Used to specify the file name which contains the query or specify the query itself. 

Inserting XML using the XSU Front End

To put an XML document in to the emp table under scott schema, use the following syntax:

java OracleXML putXML -user "scott/tiger" -fileName "/tmp/temp.xml" "emp"

This performs the following tasks:

OracleXML - putXML Options

Table 4-2 lists the putXML options:

:

Table 4-2 OracleXML -putXML Options
-putXML Options  Description 

-user "<username>/<password>" 

Used to specify the user name and password to connect to the database. If this is not specified, the user defaults to "scott/tiger". Note that he connect string is also being specified, the user name and password can be specified as part of the connect string. 

-conn "<JDBC_connect_string>" 

Used to specify the JDBC database connect string. By default the connect string is: "jdbc:oracle:oci8:@"): 

-batchSize "<batching size>" 

Used to specify the batch size, which control the number of rows which are batched together and inserted in a single trip to the database. Batching improves performance. 

-commitBatch "<commit size>" 

Used to specify the number of inserted records after which a commit is to be executed. Note that if the autocommit is true (default), then setting the commitBatch has no consequence. 

-rowTag "<tag_name>" 

Used to specify the row tag (the tag used to enclose the data coresponding to a database row). The default row tag is ROW. Specifying an empty string for the row tag tells the XSU that no row enclosing tag is used in the XML document. 

-dateFormat "<date format>" 

Used to specify the date format for the date values in the XML document. 

-ignoreCase 

Used to make the matching of the column names with tag names case insensitive (e.g. "EmpNo" will match with "EMPNO" if ignoreCase is on). 

-fileName "<file name>" | -URL "<url>" | -xmlDoc "<xml document>" 

Used to specify the XML document to insert. The fileName option specifies a local file, the URL specifies a URL to fetch the document from and the xmlDoc option inlines the XML document as a string on the command line. 

<tableName> 

The name of the table to put the values into. 

XSU Java API

The following two classes make up the XML-SQL Utility Java API:

You can find the full Java API documentation here.

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:

  1. First create a connection

  2. Create an OracleXMLQuery instance by supplying a SQL string or a ResultSet object

  3. 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


Text description of adxml032.gif follows

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


Text description of adxml014.gif follows

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.

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:

  1. Store this in a file called testXMLSQL.java

  2. Compile it using javac-the Java compiler

  3. 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;
   }
}

Paginating Results: skipRows and maxRows

In the examples shown so far, the XML-SQL Utility (XSU) takes the ResultSet or the query and generates the whole document from all the rows of the query. For getting say, 100 rows at a time, the user would then have to fire off different queries to get the first 100 rows, the next 100 and so on. Also it is not possible to skip say the first 5 rows of the query and then generate the Result. For getting these desired results, use XSU's skipRows and maxRows settings.

The skipRows parameter when set will force the generation to skip the desired number of rows before starting to generate the result. The maxRows on the other hand, would limit the number of rows that are converted to XML. If you set the skipRows to a value of 5 and maxRows to a value of 10, then the utility would skip the first 5 rows, and then generate the XML for the next 10 rows.

Keeping the Object Open

In web scenarios, you might want to keep the query object open for the duration of the user's session. For example, take the case of a web search engine which gives the results of a user's search in a paginated fashion. The first page lists 10 results, the next page lists 10 more results and so on. To achieve this, ask the utility to convert 10 rows at a time and to keep the ResultSet state alive, so that the next time we ask it for more results, it will start generating from the place the last generation finished.

When the Number of Rows or Columns in a Row Are Too Large

There is also the case that the number of rows, or the number of columns in a row may be very large. In this case, you can generate multiple documents each of a smaller size.

These cases can be handled by using the maxRows parameter and using the keepObjectOpen functionality.

keepObjectOpen Function

Typically, as soon as all results are generated, OracleXMLQuery internally closes the ResultSet, if it created one using the SQL query string given, since it assumes you no longer want any more results. However, in the case described above, we need to maintain that state, so we need to call the keepObjectOpen function to keep the cursor alive.

XSU Example 3. Paginating Results: Generating an XML Page When Called (Java)

The following example, writes a simple class which maintains the state and generates the next page every time it is called.

import org.w3c.dom.*;
import oracle.xml.parser.v2.*;
import java.sql.*;
import oracle.xml.sql.query.OracleXMLQuery;
import java.io.*;
public class pageTest
{
   Connection conn;
   OracleXMLQuery qry;
   ResultSet rset;
   Statement stmt;
   int lastRow = 0;

   public pageTest(String sqlQuery)
   {
     try{
     conn  = getConnection("scott","tiger");
     //stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
     //              ResultSet.CONCUR_READ_ONLY);// create a scrollable Rset     
     //stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
     //              ResultSet.CONCUR_READ_ONLY);// create a scrollable Rset     
     stmt = conn.createStatement();
     ResultSet rset = stmt.executeQuery(sqlQuery);  // get the result set..
     rset.first();
     qry = new OracleXMLQuery(conn,rset);   // create a OracleXMLQuery instance
     qry.keepCursorState(true); // Don't lose state after the first fetch
     qry.setRaiseNoRowsException(true);
     qry.setRaiseException(true);
     }catch(SQLException e){
      System.out.println(e.toString());
     }
   }
  
   // Returns the next XML page..!
   public String getResult(int startRow, int endRow)  throws SQLException
   {
     //rset.relative(lastRow-startRow);  // scroll inside the result set
     //rset.absolute(startRow);  // scroll inside the result set
     qry.setMaxRows(endRow-startRow); // set the max # of rows to retrieve..!
     //System.out.println("before getxml");
     return qry.getXMLString();
   }

   // Function to still perform the next page. 
   public String nextPage() throws SQLException
   {
     String result = getResult(lastRow,lastRow+10);
     lastRow+= 10;
     return result;
   }

   public void close() throws SQLException
   {
     stmt.close();   // close the statement..
     conn.close();   // close the connection
     qry.close();    // close the query..
   }
  
   public static void main(String[] argv)
   {
     String str;

     try{
     pageTest test = new pageTest("select e.* from emp e");

     int i = 0;
     // Get the data one page at a time..!!!!!
     while ((str = test.getResult(i,i+10))!= null)
     {
         System.out.println(str);
         i+= 10;
     }
     test.close();
    }catch(Exception e){
     e.printStackTrace(System.out);
    }
   }
 // 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;
   }

}

Generating XML from ResultSet Objects

We saw how we can supply a SQL query and get the results as XML. In the last example, we saw how we can retrieve results in a paginated fashion. However in web cases, we might want to retrieve the previous page and not just the next page of results. To provide this scrollable functionality, we can use the Scrollable ResultSet. Use the ResultSet object to move back and forth within the result set and use the utility to generate the XML everytime.

XSU Example 4: Generating XML from JDBC ResultSets (Java)

We will show how to use the JDBC ResultSet and generate XML from that. Note that using the ResultSet might be necessary in cases which are not handled directly by the utility (for example, setting the batch size, binding values,...) We will extend the previously defined pageTest class so that we handle any page.

public class pageTest()
{
   Connection conn;
   OracleXMLQuery qry;
   ResultSet rset;
   int lastRow = 0;

   public pageTest(String sqlQuery)
   {
     conn  = getConnection("scott","tiger");
     Statement stmt = conn.createStatement(sqlQuery);// create a scrollable 
Rset

     ResultSet rset = stmt.executeQuery();  // get the result set..
     qry = new OracleXMLQuery(conn,rset);   // create a OracleXMLQuery 
instance
     qry.keepObjectOpen(true); // Don't lose state after the first fetch
   }
  
   // Returns the next XML page..!
   public String getResult(int startRow, int endRow)
   {
     rset.scroll(lastRow-startRow);  // scroll inside the result set
     qry.setMaxRows(endRow-startRow); // set the max # of rows to 
retrieve..!
     return qry.getXMLString();
   }

   // Function to still perform the next page. 
   public String nextPage()
   {
     String result = getResult(lastRow,lastRow+10);
     lastRow+= 10;
     return result;
   }

   public void close()
   {
     stmt.close();   // close the statement..
     conn.close();   // close the connection
     qry.close();    // close the query..
   }
  
   public void main(String[] argv)
   {
     pageTest test = new pageTest("select * from emp");

     int i = 0;
     // Get the data one page at a time..!!!!!
     while ((str = test.getResult(i,i+10))!= null)
     {
         System.out.println(str);
         i+= 10;
     }
     test.close();
   }
}
 

XSU Example 5: Generating XML from Procedure Return Values (REF CURSORS) (Java)

The OracleXMLQuery class provides XML conversion only for query string or for ResultSets. But in your application if you had PL/SQL procedures which returned REF cursors, how would you do the conversion?

In this case, you can use the above mentioned ResultSet conversion mechanism to perform the task. REF cursors are references to cursor objects in PL/SQL. These cursor objects are valid SQL statements which can be iterated upon to get a set of values. These REF cursors are converted in to OracleResultSet objects in the Java world.

You can execute these procedures, get the OracleResultSet object and then send that in to the OracleXMLQuery object to get the desired XML.

Take this PL/SQL function which defines a REF cursor and returns it:

CREATE OR REPLACE package body testRef is

  function testRefCur RETURN empREF is
  a empREF;
  begin 
      OPEN a FOR select * from scott.emp; 
      return a;
  end;
end;
/

Now, everytime this function is called, it opens a cursor object for the query, "select * from emp" and returns that cursor instance. If you wanted to convert this to XML, you can do the following:

import org.w3c.dom.*;
import oracle.xml.parser.v2.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.xml.sql.query.OracleXMLQuery;
import java.io.*;
public class REFCURtest
{
   public static void main(String[] argv)
     throws SQLException
   { 
      String str;
      Connection conn  = getConnection("scott","tiger"); // create connection

      // Create a ResultSet object by calling the PL/SQL function
      CallableStatement stmt =
         conn.prepareCall("begin ? := testRef.testRefCur(); end;");

      stmt.registerOutParameter(1,OracleTypes.CURSOR); // set the define type

      stmt.execute();   // Execute the statement.
      ResultSet rset = (ResultSet)stmt.getObject(1);  // Get the ResultSet

      OracleXMLQuery qry = new OracleXMLQuery(conn,rset); // prepare Query class
      qry.setRaiseNoRowsException(true);
      qry.setRaiseException(true);
      qry.keepCursorState(true);        // set options (keep the cursor alive..
      while ((str = qry.getXMLString())!= null)
           System.out.println(str);

      qry.close();    // close the query..!

      // Note since we supplied the statement and resultset, closing the
      // OracleXMLquery instance will not close these. We would need to 
      // explicitly close this ourselves..!
      stmt.close();
      conn.close();
   }
    // 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;
   }

}

To apply the stylesheet on the other hand, use the applyStylesheet() command. This forces the stylesheet to be applied before generating the output.

Raising No Rows Exception

When there are no rows to process the utility simply returns a null string. But it might be desirable to get an exception everytime there are no more rows present, so that the application can process this through exception handlers. When the setRaiseNoRowsException() is set, then whenever there are no rows to generate for the output the utility raises a oracle.xml.sql.OracleXMLSQLNoRowsException. This is a run time exception and need not be caught unless needed. The following code extends the previous examples to use the Exception instead of checking for null strings.

XSU Example 6: No Rows Exception (Java)

public class pageTest { 
    .... // rest of the class definitions....

   public void main(String[] argv)
   {
     pageTest test = new pageTest("select * from emp");

     test.query.setRaiseNoRowsException(true); // ask it to generate 
exceptions
     try
     {
        while(true)
         System.out.println(test.nextPage());
     } 
     catch(oracle.xml.sql.OracleXMLNoRowsException)
     {
       System.out.println(" END OF OUTPUT ");  
       test.close();
     }
   }
}


Note:

Notice how the condition to check the termination, changed from checking for the result to be NULL to an exception handler. 


Storing XML

Now that you have seen how queries can be converted to XML, observe how you can put the XML back into the tables or views using the utility. The class oracle.xml.sql.dml.OracleXMLSave provides such functionality. It provides methods to insert the XML into tables, update existing tables with the XML document and to delete rows from the table based on the XML element values.

In all these cases the given XML document is parsed, the elements examined to match the tag names to those of the column names in the target table or view. The elements are then converted to the SQL types and then bound to the appropriate statement. The process and options for storing XML using the XSU are shown in Figure 4-5.

Figure 4-5 Storing XML in the Database Using the XML-SQL Utility: Process and Options


Text description of adxml013.gif follows

The document is assumed to contain a list of ROW elements each of which constitute a separate DML operation, namely, insert, update or delete on the table or view.

Insert Processing

The steps to insert a document into a table or view is to simply supply the table or the view name and then the document. The utility parses the document (if a string is given) and then creates an insert statement which it binds all the values into. By default, the utility inserts values into all the columns of the table or view and an absent element is treated as a NULL value. The following code shows how the document generated from the emp table can be put back into it with relative ease.

XSU Example 7: Inserting XML Values into all Columns (Java)

This example inserts XML values into all columns:

import java.sql.*;
import oracle.xml.sql.dml.OracleXMLSave;
public class testInsert
{
   public static void main(String argv[])
     throws SQLException
  {
    Connection conn = getConnection("scott","tiger");
    OracleXMLSave sav = new OracleXMLSave(conn, "scott.emp");
  // Assume that the user passes in this document. Save it in to the table.!
      sav.insertXML(argv[0]);
      sav.close();
   }
  // 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;
   }
}

An insert statement of the form:

insert into scott.emp (EMPNO, ENAME, JOB, MGR, SAL, DEPTNO) VALUES(?,?,?,?,?,?);

will be generated and the element tags in the input XML document matching the column names will be matched and their values bound. If you sned the code snippet shown above, to the following XML document:

 <?xml version='1.0'?>
<ROWSET>
	 	 <ROW num="1">
 	 	  <EMPNO>7369</EMPNO>
    <ENAME>Smith</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>12/17/1980 0:0:0</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </ROW>
  <!-- additional rows ... -->
</ROWSET>

you would have a new row in the emp table containing the values (7369, Smith, CLERK, 7902, 12/17/1980,800,20). Any element absent inside the row element would have been taken as a null value.

XSU Example 8: Inserting XML Values into Only Certain Columns (Java)

In certain cases, you may not want to insert values into all columns. This may be true when the values that you are getting is not the complete set and you need triggers or default values to be used for the rest of the columns. The code below shows how this can be done.

Assume that you are getting the values only for the employee number, name and job and the salary, manager, deptno and hiredate field gets filled in automatically. First create a list of column names that you want the insert to work on and then pass it to the OracleXMLSave instance.

import java.sql.*;
import oracle.xml.sql.dml.OracleXMLSave;
public class testInsert
{
   public static void main(String argv[])
     throws SQLException
   {
      Connection conn = getConnection("scott","tiger");
      OracleXMLSave sav = new OracleXMLSave(conn, "scott.emp");

      String [] colNames = new String[5];
      colNames[0] = "EMPNO";
      colNames[1] = "ENAME";
      colNames[2] = "JOB";

      sav.setUpdateColumnList(colNames); // set the columns to update..!

      // Assume that the user passes in this document as the first argument!
      sav.insertXML(argv[0]);
      sav.close();
   }
   // 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;
   }
}

An insert statement of the form,

insert into scott.emp (EMPNO, ENAME, JOB) VALUES (?, ?, ?); 

is generated. Note that in the above example, if the inserted document contains values for the other columns (JOB, HIREDATE etc.), those will be ignored.

Also an insert is performed for each ROW element that is present in the input. These inserts are batched by default.

Update Processing

Now that you know how to insert values into the table from XML documents, let us see how to update only certain values. If you get an XML document to update the salary of an employee and also the department that she works in,

<ROWSET>
	 	 <ROW num="1">
 	 	  <EMPNO>7369</EMPNO>
    <SAL>1800</SAL>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>2290</EMPNO>
    <SAL>2000</SAL>
    <HIREDATE>12/31/1992</HIREDATE>
  <!-- additional rows ... -->
</ROWSET>

you can call the update processing to update the values. In the case of update, you need to supply the utility with the list of key column names. These form part of the where clause in the update statement. In the emp table shown above, the employee number (EMPNO) column forms the key and you use that for updates.

XSU Example 9: Updating Using the keyColumns (Java)

This example updates the emp table using keyColumns:

import java.sql.*;
import oracle.xml.sql.dml.OracleXMLSave;
public class testUpdate
{
   public static void main(String argv[])
     throws SQLException
   {
      Connection conn = getConnection("scott","tiger");
      OracleXMLSave sav = new OracleXMLSave(conn, "scott.emp");

      String [] keyColNames = new String[1];
      keyColNames[0] = "EMPNO";
      sav.setKeyColumnList(keyColNames);

      // Assume that the user passes in this document as the first argument!
      sav.updateXML(argv[0]);
      sav.close();
   }
   // 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;
   }
}

In this example, two update statements would be generated. For the first ROW element, you would generate an update statement to update the SAL and JOB fields as shown below:

update scott.emp SET SAL = 1800 and DEPTNO = 30 WHERE EMPNO = 7369;

and for the second ROW element,

update scott.emp SET SAL = 2000 and HIREDATE = 12/31/1992 WHERE EMPNO = 2290;

XSU Example 10: Updating a Specified List of Columns (Java)

However, in a lot of cases you might want to specify the list of columns to update. This would speed up the processing since the same update statement can be used for all the ROW elements. Also you can ignore other tags which occur in the document.


Note:

When you specify a list of columns to update, an element corresponding to one of the update columns, if absent, will be treated as NULL. 


If you know that all the elements to be updated are the same for all the ROW elements in the XML document, then you can use the setUpdateColumnNames() function to set the list of columns to update.

import java.sql.*;
import oracle.xml.sql.dml.OracleXMLSave;
public class testUpdate
{
   public static void main(String argv[])
     throws SQLException
   {
      Connection conn = getConnection("scott","tiger");
      OracleXMLSave sav = new OracleXMLSave(conn, "scott.emp");

      String [] keyColNames = new String[1];
      keyColNames[0] = "EMPNO";
      sav.setKeyColumnList(keyColNames);

      // you create the list of columns to update..!
      // Note that if you do not supply this, then for each ROW element in the
      // XML document, you would generate a new update statement to update all
      // the tag values (other than the key columns)present in that element.
      String[] updateColNames = new String[2];
      updateColNames[0] = "SAL";
      updateColNames[1] = "JOB";
      sav.setUpdateColumnList(updateColNames); // set the columns to update..!

      // Assume that the user passes in this document as the first argument!
      sav.updateXML(argv[0]);
      sav.close();
   }
   // 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;
   }
}

Delete Processing

In the case of delete, you can set the list of key columns. These columns will be put as part of the where clause of the delete. If the key column names are not supplied, then a new delete statement will be created for each ROW element of the XML document where the list of columns in the where clause of the delete will match those in the ROW element.

XSU Example 11: Deleting Operations Per ROW (Java)

Consider the delete example shown below,

import java.sql.*;
import oracle.xml.sql.dml.OracleXMLSave;
public class testDelete
{
   public static void main(String argv[])
     throws SQLException
   {
      Connection conn = getConnection("scott","tiger");
      OracleXMLSave sav = new OracleXMLSave(conn, "scott.emp");

      // Assume that the user passes in this document as the first argument!
      sav.deleteXML(argv[0]);
      sav.close();
   }
   // 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;
   }
}

If you use the same XML document shown for the update example, you would end up with two delete statements,

DELETE FROM scott.emp WHERE empno=7369 and sal=1800 and deptno=30; 
DELETE FROM scott.emp WHERE empno=2200 and sal=2000 and hiredate=12/31/1992;

The delete statements were formed based on the tag names present in each ROW element in the XML document.

XSU Example 12: Deleting Specified Key Values (Java)

If you instead want the delete to only use the key values as predicates, you can use the setKeyColumn function to set this.

import java.sql.*;
import oracle.xml.sql.dml.OracleXMLSave;
public class testDelete
{
   public static void main(String argv[])
     throws SQLException
   {
      Connection conn = getConnection("scott","tiger");
      OracleXMLSave sav = new OracleXMLSave(conn, "scott.emp");

      String [] keyColNames = new String[1];
      keyColNames[0] = "EMPNO";
      sav.setKeyColumnList(keyColNames);

      // Assume that the user passes in this document as the first argument!
      sav.deleteXML(argv[0]);
      sav.close();
   }
   // 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;
   }
}

Here a single delete statement of the form:

DELETE FROM scott.emp WHERE EMPNO=?

will be generated and used for all ROW elements in the document.

XSU PL/SQL API

The XML-SQL Utility PL/SQL API reflects the Java API in the generation and storage. The DBMS_XMLQuery and DBMS_XMLSave are the two packages that reflect the functions in the java classes - OracleXMLQuery and OracleXMLSave.

Both these packages have a context handle associated with them. Create a context by calling one of the constructor-like functions to get the handle and then use the handle in all subsequent calls.

Generating XML with DBMS_XMLQuery()

Generating XML results in a CLOB that contains the XML document. To use DBMS_XMLQuery's and the generation engine follow these steps:

  1. Create a context handle by calling the DBMS_XMLQuery.getCtx function and supplying it the query (either as a CLOB or a VARCHAR2)

  2. Bind possible values to the query using the DBMS_XMLQuery.bind function. The binds work by binding a name to the position. For example, the query can be something like, select * from emp where empno = :EMPNO_VAR. Here the user binds the value for the EMPNO_VAR using the setBindValue function.

  3. Set optional arguments like the ROW tag name, the ROWSET tag name or the number of rows to fetch etc.

  4. Fetch the XML as a CLOB using the getXML() functions. The getXML can be called to generate the XML with or without a DTD.

  5. Close the context.

Here are some examples that use the DBMS_XMLQuery PL/SQL package.

XSU Example 13: Generating XML From Simple Queries (PL/SQL)

In this example, you will try to select rows from the emp table and get a XML document as a CLOB. You first get the context handle by passing in a query and then call the getXMLClob routine to get the CLOB value. The document will be in the same encoding as that of the database character set.

declare
  queryCtx DBMS_XMLquery.ctxType;
  result CLOB;
begin

  -- set up the query context...!
  queryCtx := DBMS_XMLQuery.newContext('select * from emp');
 
  -- get the result..!
  result := DBMS_XMLQuery.getXML(queryCtx);
  -- Now you can use the result to put it in tables/send as messages..
  printClobOut(result);
  DBMS_XMLQuery.closeContext(queryCtx);  -- you must close the query handle..
end;
/

XSU Example 13a: Printing CLOB to Output Buffer

The printClobOut() is a simple procedure that prints the CLOB to the output buffer. If you run this PL/SQL code in SQL*Plus, you will see the result of the CLOB being printed out to screen. Set the serveroutput to on in order to see the results.

The printClobOut is shown below:-

/CREATE OR REPLACE PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is
xmlstr varchar2(32767);
line varchar2(2000);
begin
  xmlstr := dbms_lob.SUBSTR(result,32767);
  loop
    exit when xmlstr is null;
    line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
    dbms_output.put_line('| '||line);
    xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
  end loop;
end;
/

XSU Example 14: Changing ROW and ROWSET Tag Names (PL/SQL)

The PL/SQL APIs also provide the ability to change the ROW and the ROWSET tag names. These are the default names that are put around each row of the result and around the whole document respectively. The procedures, setRowTagName and setRowSetTagName accomplish this as shown below:

--Setting the ROW tag names

declare
   queryCtx DBMS_XMLQuery.ctxType;
   result CLOB;
begin
   -- set the query context.
   queryCtx := DBMS_XMLQuery.newContext('select * from emp');
 
   DBMS_XMLQuery.setRowTag(queryCtx,'EMP'); -- sets the row tag name
   DBMS_XMLQuery.setRowSetTag(queryCtx,'EMPSET'); -- sets rowset tag name

   result := DBMS_XMLQuery.getXML(queryCtx); -- get the result

   printClobOut(result);  -- print the result..!
   DBMS_XMLQuery.closeContext(queryCtx);  -- close the query handle;
end;
/

The resulting XML document has an EMPSET document element and each row separated using the EMP tag.

XSU Example 15: Paginating Results Using setMaxRows() and setSkipRows()

The results from the query generation can be paginated by using the setMaxRows and setSkipRows functions. The former sets the maximum number of rows to be converted to XML. This is relative to the current row position from which the last result was generated. The skipRows parameter specifies the number of rows to skip before converting the row values to XML. For example, to skip the first 3 rows of the emp table and then print out the rest of the rows 10 at a time, you can set the skipRows to 3 for the first batch of 10 rows and then set skipRows to 0 for the rest of the batches.

As in the case of the XML-SQL Utility Java API, call the keepObjectOpen() function to make sure that the state is maintained between fetches. The default behavior is to close the state after a fetch is done. In the case of multiple fetches, you need to figure out when there are no more rows to fetch. This can be done by setting the setRaiseNoRowsException(). This causes an exception to be raised if no rows are written to the CLOB. This can be caught and used as the termination condition.

-- Pagination of results

declare
  queryCtx DBMS_XMLquery.ctxType;
  result CLOB;
begin

  -- set up the query context...!
  queryCtx := DBMS_XMLQuery.newContext('select * from emp');
 
  DBMS_XMLQuery.setSkipRows(queryCtx,3); -- set the number of rows to skip
  DBMS_XMLQuery.setMaxRows(queryCtx,10); -- set the max number of rows per fetch

  result := DBMS_XMLQuery.getXML(queryCtx); -- get the first result..!

  printClobOut(result); -- print the result out.. This is you own routine..!
  DBMS_XMLQuery.setSkipRows(queryCtx,0); -- from now don't skip any more rows..!

  DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,true);
                                         -- raise no rows exception..!
  begin
    loop  -- loop forever..!
      result := DBMS_XMLQuery.getXML(queryCtx); -- get the next batch 
      printClobOut(result);             -- print the next batch of 10 rows..!
    end loop;
  exception
    when others then
    -- dbms_output.put_line(sqlerrm);
       null; -- termination condition, nothing to do;
  end;
  DBMS_XMLQuery.closeContext(queryCtx);  -- close the handle..!
end;
/

Setting Stylesheets in XSU (PL/SQL)

The PL/SQL API provides the ability to set the stylesheet header in the result XML or apply a stylesheet itself to the result XML document, before generation. The latter is a huge performance win since otherwise the XML document has to be generated as a CLOB, sent to the parser again and then the stylesheet applied. In this case, internally the utility generates a DOM document, calls the parser, applies the stylesheet and then generates the result.

The procedure, setStylesheetHeader(), sets the stylesheet header in the result. This simply adds the XML processing instruction to include the stylesheet.

The useStyleSheet() procedure, on the other hand, uses the stylesheet to generate the result.

Binding Values in XSU (PL/SQL)

The PL/SQL API provides the ability to bind values to the SQL statement. The SQL statement can contain named bind variables. The variables have to start with a ':' in front of them to signal that they are bind variables. The steps involved in using the bind variable is as follows,

  1. Initialize the query context with the query containing the bind variables. For example, the following statement registers a query to select the rows from the emp table with the where clause containing the bind variables :EMPNO and :ENAME which you will bind the values for employee number and employee name later.

    queryCtx = DBMS_XMLQuery.getCtx('select * from emp where empno = :EMPNO and 
    ename = :ENAME');
    
    
  2. Set the list of bind values. The clearBindValues() clears all the bind variables set. The setBindValue() sets a single bind variable with a string value. For example, you will set the empno and ename values as shown below:-

    DBMS_XMLQuery.clearBindValues(queryCtx);
    DBMS_XMLQuery.setBindValue(queryCtx,'EMPNO',20);
    DBMS_XMLQuery.setBindValue(queryCtx,'ENAME','John');
    
    
  3. Fetch the results. This will apply the bind values to the statement and then get the result corresponding to the predicate empno = 20 and ename = 'John'.

    DBMS_XMLQuery.getXMLClob(queryCtx);
    
    
  4. Re-bind values if necessary, For example to change the ENAME alone to "scott" and re-execute the query,

    DBMS_XMLQuery.setBindValue(queryCtx,'ENAME','Scott');
    
    

The rebinding of ENAME will now use Scott instead of John.

XSU Example 15a: Binding Values to the SQL Statement

The following example illustrates the use of bind variables in the SQL statement:

declare
  queryCtx DBMS_XMLquery.ctxType;
  result CLOB;
begin

queryCtx := DBMS_XMLQuery.newContext(
       'select * from emp where empno = :EMPNO and ename = :ENAME');

DBMS_XMLQuery.clearBindValues(queryCtx);
DBMS_XMLQuery.setBindValue(queryCtx,'EMPNO',7566);
DBMS_XMLQuery.setBindValue(queryCtx,'ENAME','JONES');

result := DBMS_XMLQuery.getXML(queryCtx);

--printClobOut(result); 

DBMS_XMLQuery.setBindValue(queryCtx,'ENAME','Scott');

result := DBMS_XMLQuery.getXML(queryCtx);

--printClobOut(result);
end;
/

Storing XML in the Database Using DBMS_XMLSave

To use DBMS_XMLSave() and the XML-SQL Utility storage engine, follow these steps:

  1. Create a context handle by calling the DBMS_XMLSave.getCtx function and supplying it the table name to use for the DML operations.

  2. In case of inserts, you can set the list of columns to insert into using the setUpdateColumn function. The default is to insert values into all the columns.

    For updates, the list of key columns must be supplied. Optionally the list of columns to update may also be supplied. In this case, the tags in the XML document matching the key column names will be used in the WHERE clause of the update statement and the tags matching the update column list will be used in the SET clause of the update statement.

    For deletes the default is to create a WHERE clause to match all the tag values present in each ROW element of the document supplied. To override this behavior you can set the list of key columns. In this case only those tag values whose tag names match these columns will be used to identify the rows to delete (in effect used in the WHERE clause of the delete statement).

  3. Supply an XML document to the insertXML, updateXML or deleteXML functions to insert, update and delete respectively.

  4. You can repeat the last operation any number of times.

  5. Close the context.

Use the same examples as for the Java case, OracleXMLSave class examples.

XSU Insert Processing in PL/SQL

The steps to insert a document into a table or view is to simply supply the table or the view name and then the document. The utility parses the document (if a string is given) and then creates an insert statement which it binds all the values into. By default, the utility inserts values into all the columns of the table or view and an absent element is treated as a NULL value.

The following code shows how the document generated from the emp table can be put back into it with relative ease.

XSU Example 16: Inserting Values into All Columns (PL/SQL)

This example creates a procedure, insProc, which takes in an XML document as a CLOB and a table name to put the document into and then inserts the document:

create or replace procedure insProc(xmlDoc IN CLOB, tableName IN VARCHAR2) is
   insCtx DBMS_XMLSave.ctxType;
   rows number;
 begin
    insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle
    rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document
    DBMS_XMLSave.closeContext(insCtx);            -- this closes the handle
end;
/

This procedure can now be called with any XML document and a table name. For example, a call of the form,

insProc(xmlDocument, 'scott.emp');

will generate an insert statement of the form,

insert into scott.emp (EMPNO, ENAME, JOB, MGR, SAL, DEPTNO) VALUES(?,?,?,?,?,?);

and the element tags in the input XML document matching the column names will be matched and their values bound. For the code snippet shown above, if you send it the XML document,

 <?xml version='1.0'?>
<ROWSET>
	 	 <ROW num="1">
 	 	  <EMPNO>7369</EMPNO>
    <ENAME>Smith</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>12/17/1980 0:0:0</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </ROW>
  <!-- additional rows ... -->
</ROWSET>

you would have a new row in the emp table containing the values (7369, Smith, CLERK, 7902, 12/17/1980,800,20). Any element absent inside the row element would have been taken as a null value.

XSU Example 17: Inserting Values into Only Certain Columns (PL/SQL)

In certain cases, you may not want to insert values into all columns. This might be true when the values that you are getting is not the complete set and you need triggers or default values to be used for the rest of the columns. The code below shows how this can be done.

Assume that you are getting the values only for the employee number, name and job and the salary, manager, deptno and hiredate field gets filled in automatically. You create a list of column names that you want the insert to work on and then pass it to the DBMS_XMLSave procedure. The setting of these values can be done by calling the setUpdateColumnName() procedure repeatedly, passing in a column name to update every time. The column name settings can be cleared using the clearUpdateColumnNames().


create or replace procedure testInsert( xmlDoc IN clob) is
  insCtx DBMS_XMLSave.ctxType;
  doc clob;
  rows number;
begin
 
   insCtx := DBMS_XMLSave.newContext('scott.emp'); -- get the save context..!

   DBMS_XMLSave.clearUpdateColumnList(insCtx); -- clear the update settings

   -- set the columns to be updated as a list of values..
   DBMS_XMLSave.setUpdateColumn(insCtx,'EMPNO'); 
   DBMS_XMLSave.setUpdateColumn(insCtx,'ENAME');
   DBMS_XMLSave.setUpdatecolumn(insCtx,'JOB');

   -- Now insert the doc. This will only insert into EMPNO,ENAME and JOB columns
   rows := DBMS_XMLSave.insertXML(insCtx, xmlDoc); 
   DBMS_XMLSave.closeContext(insCtx);

end;
/

If you call the procedure passing in a CLOB as a document, an insert statement of the form,

insert into scott.emp (EMPNO, ENAME, JOB) VALUES (?, ?, ?); 

is generated. Note that in the above example, if the inserted document contains values for the other columns (JOB, HIREDATE etc.), those will be ignored.

Also an insert is performed for each ROW element that is present in the input. These inserts are batched by default.

Update Processing

Now that you know how to insert values into the table from XML documents, let us see how to update only certain values. If you get an XML document to update the salary of an employee and also the department that she works in:

<ROWSET>
	 	 <ROW num="1">
 	 	  <EMPNO>7369</EMPNO>
    <SAL>1800</SAL>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>2290</EMPNO>
    <SAL>2000</SAL>
    <HIREDATE>12/31/1992</HIREDATE>
  <!-- additional rows ... -->
</ROWSET>

you can call the update processing to update the values. In the case of update, you need to supply the utility with the list of key column names. These form part of the where clause in the update statement. In the emp table shown above, the employee number (EMPNO) column forms the key and you use that for updates.

XSU Example 18: Updating an XML Document Using keyColumns(PL/SQL)

,.......

create or replace procedure testUpdate ( xmlDoc IN clob) is
  updCtx DBMS_XMLSave.ctxType; 
  rows number;
begin
   
   updCtx := DBMS_XMLSave.newContext('scott.emp');  -- get the context
   DBMS_XMLSave.clearUpdateColumnList(updCtx); -- clear the update settings..
  
   DBMS_XMLSave.setKeyColumn(updCtx,'EMPNO'); -- set EMPNO as key column
   rows := DBMS_XMLSave.updateXML(updCtx,xmlDoc);  -- update the table.
   DBMS_XMLSave.closeContext(updCtx);             -- close the context..!

end;
/

In this example, when the procedure is executed with a CLOB value that contains the document described above, two update statements would be generated. For the first ROW element, you would generate an update statement to update the SAL and JOB fields as shown below:-

update scott.emp SET SAL = 1800 and DEPTNO = 30 WHERE EMPNO = 7369;

and for the second ROW element,

update scott.emp SET SAL = 2000 and HIREDATE = 12/31/1992 WHERE EMPNO = 2290;

XSU Example 19: Specifying a List of Columns to Update (PL/SQL)

However, in a lot of cases you might want to specify the list of columns to update. This would speed up the processing since the same update statement can be used for all the ROW elements. Also you can ignore other tags which occur in the document. Note that when you specify a list of columns to update, an element corresponding to one of the update columns, if absent, will be treated as NULL.

If you know that all the elements to be updated are the same for all the ROW elements in the XML document, then you can use the setUpdateColumnName() procedure to set the column name to update.

create or replace procedure testUpdate(xmlDoc IN CLOB) is
  updCtx DBMS_XMLSave.ctxType;
  rows number;
begin
  
   updCtx := DBMS_XMLSave.newContext('scott.emp');
   DBMS_XMLSave.setKeyColumn(updCtx,'EMPNO'); -- set EMPNO as key column

   -- set list of columnst to update.
   DBMS_XMLSave.setUpdateColumn(updCtx,'SAL');
   DBMS_XMLSave.setUpdateColumn(updCtx,'JOB');

   rows := DBMS_XMLSave.updateXML(updCtx,xmlDoc); -- update the XML document..!
   DBMS_XMLSave.closeContext(updCtx);   -- close the handle

end;
/

Delete Processing

In the case of delete, you can set the list of key columns. These columns will be put as part of the where clause of the delete. If the key column names are not supplied, then a new delete statement will be created for each ROW element of the XML document where the list of columns in the where clause of the delete will match those in the ROW element.

XSU Example 20: Deleting Operations per ROW (PL/SQL)

Consider the delete example shown below:

create or replace procedure testDelete(xmlDoc IN clob) is
  delCtx DBMS_XMLSave.ctxType;
  rows number;
begin

   delCtx  := DBMS_XMLSave.newContext('scott.emp');
   DBMS_XMLSave.setKeyColumn(delCtx,'EMPNO');

   rows := DBMS_XMLSave.deleteXML(delCtx,xmlDoc);
   DBMS_XMLSave.closeContext(delCtx);
end;
/

If you use the same XML document shown for the update example, you would end up with two delete statements,

DELETE FROM scott.emp WHERE empno=7369 and sal=1800 and deptno=30; 
DELETE FROM scott.emp WHERE empno=2200 and sal=2000 and hiredate=12/31/1992;

The delete statements were formed based on the tag names present in each ROW element in the XML document.

XSU Example 21: Deleting by Specifying the Key Values (PL/SQL)

If you instead want the delete to only use the key values as predicates, you can use the setKeyColumn function to set this.

create or replace package testDML AS
   saveCtx DBMS_XMLSave.ctxType := null;   -- a single static variable

   procedure insertXML(xmlDoc in clob);
   procedure updateXML(xmlDoc in clob);
   procedure deleteXML(xmlDoc in clob);

 end;
/

create or replace package body testDML AS
  
  rows number;
  
  procedure insertXML(xmlDoc in clob) is
  begin
    rows := DBMS_XMLSave.insertXML(saveCtx,xmlDoc);
  end;
  
  procedure updateXML(xmlDoc in clob) is
  begin
    rows := DBMS_XMLSave.updateXML(saveCtx,xmlDoc);
  end;
  
  procedure deleteXML(xmlDoc in clob) is
  begin
    rows := DBMS_XMLSave.deleteXML(saveCtx,xmlDoc);
  end;

begin
  saveCtx := DBMS_XMLSave.newContext('scott.emp'); -- create the context once..!
  DBMS_XMLSave.setKeyColumn(saveCtx, 'EMPNO');      -- set the key column name.
end;
/

Here a single delete statement of the form,

DELETE FROM scott.emp WHERE EMPNO=?

will be generated and used for all ROW elements in the document.

XSU Example 22: ReUsing the Context Handle (PL/SQL)

In all the three cases described above, insert, update and delete, the same context handle can be used to do more than one operation. i.e. one can perform more than one insert using the same context provided all of those inserts are going to the same table that was specified when creating the save context. The context can also be used to mix updates, deletes and inserts.

For example, the following code shows how one can use the same context and settings to insert, delete or update values depending on the user's input.

The example uses a package static variable to store the context so that the same context can be used for all the function calls.

create or replace package testDML AS
   saveCtx DBMS_XMLSave.ctxType := null;   -- a single static variable

   procedure insert(xmlDoc in clob);
   procedure update(xmlDoc in clob);
   procedure delete(xmlDoc in clob);

 end;
/

create or replace package body testDML AS
  
  procedure insert(xmlDoc in clob) is
  begin
    DBMS_XMLSave.insertXML(saveCtx, xmlDoc);
  end;
  
  procedure update(xmlDoc in clob) is
  begin
    DBMS_XMLSave.updateXML(saveCtx, xmlDoc);
  end;
  
  procedure delete(xmlDoc in clob) is
  begin
    DBMS_XMLSave.deleteXML(saveCtx, xmlDoc);
  end;

  begin
    saveCtx := DBMS_XMLSave.newContext('scott.emp'); -- create the context 
once..!
    DBMS_XMLSave.setKeyColumn(saveCtx, 'EMPNO');   -- set the key column name.
  end;
end;
/

In the above package, you create a context once for the whole package (thus the session) and then reuse the same context for performing inserts, udpates and deletes.


Note:

The key column ('EMPNO') would be used both for updates and deletes as a way of identifying the row. 


Users of this package can now call any of the three routines to update the emp table:

testDML.insert(xmlclob);
testDML.delete(xmlclob);
testDML.update(xmlclob);

All of these calls would use the same context. This would improve the performance of these operations, particularly if these operations are performed frequently.

Advanced Usage Techniques

Exception Handling in Java

OracleXMLSQLException class

The utility catches all exceptions that occur during processing and throws an oracle.xml.sql.OracleXMLSQLException which is a run time exception. The calling program thus does not have to catch this exception all the time. If the program can still catch this exception and do the appropriate action. The exception class provides functions to get the error message and also get the parent exception, if any. For example, the program shown below, catches the run time exception and then gets the parent exception.

OracleXMLNoRowsException class

This exception is generated when the setRaiseNoRowsException is set in the OracleXMLQuery class during generation. This is a subclass of the OracleXMLSQLException class and can be used as an indicator of the end of row processing during generation.

import java.sql.*;
import oracle.xml.sql.query.OracleXMLQuery;

public class testException
{
   public static void main(String argv[])
     throws SQLException
   {
      Connection conn = getConnection("scott","tiger");

      // wrong query this will generate an exception
      OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from emp where sd
 = 322323");

      qry.setRaiseException(true); // ask it to raise exceptions..!

      try{
        String str = qry.getXMLString();
      }catch(oracle.xml.sql.OracleXMLSQLException e)
      {
        // Get the original exception
        Exception parent = e.getParentException();
        if (parent instanceof java.sql.SQLException)
        {
           // perform some other stuff. Here you simply print it out..
           System.out.println(" Caught SQL Exception:"+parent.getMessage());
        }
        else
          System.out.println(" Exception caught..!"+e.getMessage());
     }
   }
    // 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;
   }

}

Exception Handling in PL/SQL

Here is a PL/SQL exception handling example:

declare
  queryCtx DBMS_XMLQuery.ctxType;
  result clob;
  errorNum NUMBER;
  errorMsg VARCHAR2(200);
begin

  queryCtx := DBMS_XMLQuery.newContext('select * from emp where df = dfdf');

  -- set the raise exception to true..
  DBMS_XMLQuery.setRaiseException(queryCtx, true);
  DBMS_XMLQuery.setRaiseNoRowsException(queryCtx, true);

  -- set propagate original exception to true to get the original exception..!
  DBMS_XMLQuery.propagateOriginalException(queryCtx,true);
  result := DBMS_XMLQuery.getXML(queryCtx);

  exception
    when others then
      -- get the original exception
      DBMS_XMLQuery.getExceptionContent(queryCtx,errorNum, errorMsg);
      dbms_output.put_line(' Exception caught ' || TO_CHAR(errorNum)
                   || errorMsg );
end; 
/

Frequently Asked Questions (FAQs): XML-SQL Utility (XSU)

What Schema Structure to Use With XSU to Store XML?

Question

I have the following XML in my customer.xml file:

<ROWSET>
 <ROW num="1">
  <CUSTOMER>
   <CUSTOMERID>1044</CUSTOMERID>
   <FIRSTNAME>Paul</FIRSTNAME>
   <LASTNAME>Astoria</LASTNAME>
   <HOMEADDRESS>
    <STREET>123 Cherry Lane</STREET>
    <CITY>SF</CITY>
    <STATE>CA</STATE>
    <ZIP>94132</ZIP>
   </HOMEADDRESS>
  </CUSTOMER>
 </ROW>
</ROWSET>

What database schema structure should I use to store this xml with XSU?

Answer

Since your example is more than one level deep (i.e. has a nested structure), you should use an object-relational schema. The XML above will canonically map to such a schema. An appropriate db. schema would be the following:

create type address_type as object
 (
 street varchar2(40),
 city varchar2(20),
 state varchar2(10),
 zip varchar2(10)
 );
 /
 create type customer_type as object
 (
customerid number(10),
firstname varchar2(20),
lastname varchar2(20),
homeaddress address_type
 );
/
create table customer_tab ( customer customer_type);

In the case you wanted to load customer.xml via the XSU into a relational schema, you could still do it by creating objects in views on top of your relational schema.

For example, you would have a relational table which would contain all the information:

create table cust_tab
 ( customerid number(10), 
   firstname varchar2(20), 
   lastname varchar2(20), 
   state varchar2(40),
   city varchar2(20), 
   state varchar2(20), 
   zip varchar2(20)
 );

Then you would create a customer view which contains a customer object on top of it, as in:

create view customer_view as
select customer_type(customerid, firstname, lastname,
address_type(state,street,city,zip))
from cust_tab;

Finally, you could flatten your XML using XSLT and then insert it directly into your relational schema. This is the least recommended option.

Storing XML Data Across Tables

Question

Can XML- SQL Utility store XML data across tables?

Answer

Currently XML-SQL Utility (XSU) can only store to a single table. It maps a canonical representation of an XML document into any table/view. But of course there is a way to store XML with the XSU across table. One can do this using XSLT to transform any document into multiple documents and insert them separately. Another way is to define views over multiple tables (object views if needed) and then do the inserts ... into the view. If the view is inherently non-updatable (because of complex joins, ...), then one can use INSTEAD-OF triggers over the views to do the inserts.

Using XML-SQL Utility to Load XML Stored in Attributes

Question

I would like to use the XML-SQL Utility to load XML where some of the data is stored in attributes; yet, the XML-SQL Utility seems to ignore the XML attributes. What can I do?

Answer

Unfortunately, for now you will have to use XSLT to transform your XML document (i.e. change your attributes into elements). The XML-SQL Utility does assume canonical mapping from XML to a db. schema. This takes away a bit from the flexibility, forcing the user to sometimes resort to XSLT, but at the same time, in the common case, it doesn't burden the user with having to specify a mapping.

XML-SQL Utility is Case Sensitive: Use ignoreCase or...

Question

I am trying to insert the following XML document (dual.xml):

<ROWSET>
   <row>
      <DUMMY>X</DUMMY>
   </row>
</ROWSET>

Into the table "dual" using the command line front end of the XSU, like in:

java OracleXML putxml -filename dual.xml dual

and I get the following error:

oracle.xml.sql.OracleXMLSQLException: No rows to modify -- the row enclosing tag 
missing.  Specify the correct row enclosing tag.

Answer

By default the XML SQL Utility is case sensitive, so it looks for the record separator tag which by default is "ROW"; yet, all it can find is "row". Another related common mistake is to case mismatch one of the element tags. For example if in dual.xml the tag "DUMMY" was actually "dummy" than the XML SQL Utility would also raise an error complaining that if couldn't find a matching column in the table "dual". So user has two options -- use the correct case or use the "ignoreCase" feature.

Generating Database Schema from a DTD

Question

Given a DTD, will the XML SQL Utility generate the database schema?

Answer

No. Due to a number of shortcomings of the DTD, this functionality is not available. Once XML Schema standard is finalized this functionality will become feasible.

Using XML-SQL Utility Command Line

Question

I am using the XML SQL Utility's command line front end, and I am passing a connect string but I get a TNS error back. Can you provide examples of a thin driver connect string and an OCI8 driver connect string?

Answer

An example of an JDBC thin driver connect string is: "jdbc:oracle:thin:<user>/<password>@<hostname>:<port number>:<DB SID>"; furthermore, the db. has to have a active TCP/IP listener. A valid OCI8 connect string would be: "jdbc:oracle:oci8:<user>/<password>@<hostname>".

Does XML-SQL Utility Commit After INSERT, DELETE, UPDATE?

Question

Does XML SQL Utility commit after it's done inserting/deleting/updating? What happens if an error occurs.

Answer

By default the XML SQL Utility executes a number of insert (or del or update) statements at a time. The number of statements batch together and executed at the same time can be overridden using the "setBatchSize" feature.

By default the XML SQL Utility does no explicit commits. If the autocommit is on (default for the JDBC connection) then after each batch of statement executions a commit happens. The user can override this by turning autocommit off and then specifying after how many statement executions should a commit occur which can be done using the "setCommitBatch" feature.

Finally, what happens if an error occurs... Well, the XSU rolls back to either the state the target table was before the particular call to the XSU, or the state right after the last commit made during the current call to the XSU.


   
Copyright © 1996-2000 Oracle Corporation.
All Rights Reserved.