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
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).
XSU can exist on any
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.
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 XSU command line options are provided through the Java class, OracleXML, which can be invoked from a command line:
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 =