The XML SQL Utility (XSU) for Java and PLSQL

The XML SQL Utility (XSU) for Java and PL/SQL

The XML SQL Utility allows developers to transform data retrieved from the Oracle database into XML, extract data from an XML document and insert the data into an Oracle database, and extract data from an XML document and apply the data to update or delete values of the appropriate columns or attributes. This is not the full list of capabilities within XSU, but it represents what will be discussed in this section.

Tasks To Complete Before Using XSU

Before you can use XSU, you will need to perform a few tasks. A valid JDBC driver must be available. XSU can work with any JDBC driver, but is only certified for use with Oracle JDBC drivers. Oracle XML Parser (included with Oracle8 i of the database and every succeeding database version) must also be available. By default (in Oracle 8.1.7 and later), the installation of XSU is performed as part of the standard installation. If you chose not to install XSU when installing your database, refer to the Oracle Application Developer s Guide - XML 10g (9.0.4) Part Number B12099-01 (http://download-west.oracle.com/docs/cd/B10464_01/web.904/b12099/toc.htm), Chapter 7, XML SQL Utility (XSU).

Where Does XSU Live ?

XSU can exist on any tier that supports Java:

• In the database The Java classes that make XSU can be loaded into Java-enabled Oracle8 i or later. XSU contains a PL/SQL wrapper that publishes the XSU Java API to PL/SQL, creating a PL/SQL API. This way you can:

  • Write new Java applications that run inside the database and that can directly access the XSU Java API

  • Write PL/SQL applications that access XSU through its PL/SQL API

  • Access XSU functionality directly through SQL

• In the middle tier Your application architecture may need to use an application server in the middle tier, separate from the database. The application tier can 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, if you want to integrate different JDBC data sources in the middle tier, you could install the XSU in your middle tier and your Java programs could make use of XSU tb server supports Java servlets. This way, you can write Java servlets that use XSU to accomplish their task.

  Tip  

  XSQL servlet is a standard servlet provided by Oracle. It is built on top of XSU and provides a template-like interface to XSU functionality. If XML processing in the web server is desired, use the XSQL servlet, as it will spare you from the intricacies of servlet programming.

• In The Client Tier XML SQL Utility can also be installed on a client system, where you can write Java programs that use XSU. You can also use XSU directly through its command line front end.

The OracleXML class

The XSU command line options are provided through the Java class, OracleXML, which can be invoked from a command line:

 java OracleXML 

By not passing any parameters, this command will print the options for using the class:

 C:\oracle\product.1.0\db_1>java OracleXML OracleXML-Error:wrong argument list Call with OracleXML getXML  [-user "username/password"]     the username and password  [-conn "JDBC_connect_string"]   JDBC connect string  [-withDTD  -withSchema]        generate the DTD/Schema  [-rowsetTag <rowsetTag>]        document tag name  [-rowTag    <rowTag>]           row element tag name  [-rowIdAttr <attrName>]         row-id attribute name  [-rowIdColumn <column_name>]    db-column to use for the row id  [-collectionIdAttr <attrName>]  collection element-id attribute  [-useTypeForCollElemTag]        use type name for coll-elem tag  [-useNullAttrId]                use a null attribute  [-styleSheet <URI>]             stylesheet processing instruction header  [-styleSheetType <type>]        stylesheet header type (e.g.text/xsl)  [-setXSLT <URI>]                XSLT to apply to XML doc  [-setXSLTRef <URI>]             XSLT external entity reference  [-useLowerCase -useUpperCase]  the case of the tag names  [-withEscaping]                 if necessary do SQL-XML name escaping  [-errorTag <errorTagName>]      error tag name  [-raiseException]               raise exceptions for errors  [-raiseNoRowsException]         raise exception if no returned  [-maxRows   <maxRows>]          maximum rows in output  [-skipRows  <skipRows>]         rows to skip in output  [-encoding <encoding_name>]     encoding to be used  [-dateFormat <date format>]     date format to be used  (<query> -fileName <sqlfile>)  SQL query  file containing the query   OR  OracleXML putXML  [-user "username/password"]     the username and password  [-conn "JDBC_connect_string"]   JDBC connect string  [-batchSize <size>]             number of inserts executed at a time  [-commitBatch <size>]           number of inserts commited at a time  [-rowTag <rowTagName>]          the name for row elements  [-dateFormat <format>]          the format of date elements  [-withEscaping]                 if necessary do SQL-XML name escaping  [-ignoreCase]                   ignore the case of the tag names  [-preserveWhitespace]           preserves any whitespaces  [-setXSLT <URI>]                XSLT to apply to XML doc  [-setXSLTRef <URI>]      erveWhitespace]           preserves any whitespaces  [-setXSLT <URI>]                XSLT to apply to XML doc  [-setXSLTRef <URI>]             external entity reference for XSLT doc  [-fileName fileName            the XML document file name or   -URL url                      URL or   -xmlDoc <XMLDocumentString>]   XML string  <tableName>                     the table name to put into 

To generate XML, we will use the getXML parameter. To generate an XML document by querying the XYZ table in the user1 schema:

 C:\oracle\product.1.0\db_1\BIN>sqlplus user1/user1@orcl SQL*Plus: Release 10.1.0.2.0 - Production on Mon Aug 23 13:06:29 2004 Copyright (c) 1982, 2004, Oracle.  All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select * from xyz; CObc                  def ghi                  jkl mno                  pqr 

we can issue the following command:

 C:\oracle\product.1.0\db_1\BIN>java OracleXML getXML -user "user1/user1" "select * from xyz" <?xml version =