H
XML SQL Utility (XSU) Specifications and Cheat Sheets

This appendix contains the following sections:

• Installing XML SQL Utility

• Requirements for Running XML SQL Utility

• XML SQL Utility (XSU) for Java, Cheat Sheets

• XML SQL Utility (XSU) for PL/SQL, Cheat Sheets

Installing XML SQL Utility

Contents of the XSU Distribution

Table H-1 lists XML SQL Utility (XSU) distribution archive (zip file) contents.

Table H-1 XSU Distribution Contents 

File (with relative location)	Description
relnotes.html	The release notes
env.csh	This files is a helper csh shell script which can set up all the environmental variables needed to run the utility correctly. The user must setup the directory information correctly (for example, point to the installed area for the JDK etc.)
env.bat	This file is the same as the env.csh except that it is written for the Windows platform.
lib/oraclexmlsql.jar	The jar file containing all the Java functions for the utility.
lib/xmlparserv2.jar	The Oracle XML parser V2 packaged with the utility.
lib/oraclexmlsqlload.csh (Unix) lib/oraclexmlsqlload.bat (Windows)	A csh and bat script to help load the utility into an Oracle database. These scripts call loadjava to load the jar file into the database and then run the xmlgenpkg.sql to create the PL/SQL front-end wrappers.
lib/xmlgenpkg.sql	This file contains the sql script for creating the PL/SQL front-end wrappers.

Installing XML SQL Utility: Procedure

To install XML SQL Utility (XSU) follow these steps:

1. Requirements. Check that you have the correct software requirements loaded.

2. Extract the XSU files

3. Set Up Your Environment Correctly: Client Side
   • CLASSPATH Settings

   • Ensure the database is up

4. Set Up Your Environment Correctly: Server Side

Installing 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:

To use the XSU's client side front-end or its java API, you need to:

1. Setup the environment (that is, set CLASSPATH...):
   • Unix users: make sure that the path names in env.csh are correct; source the env.csh. If you are using a shell other than csh or tcsh, you will have to edit the file to use your shell's syntax.

   • Windows users: make sure that the path names in env.bat are correct; execute the file.

To use XSU's PL/SQL API, or write java stored procedures on top of XSU's java API, you need to:

1. Confirm that the USER_PASSWORD macro in xsulload.xxx names the desired schema into which the XSU is to be loaded (default "scott/tiger").
   • Unix users: look into xsulload.csh

   • Windows users: look into xsuload.bat

2. Confirm that the Oracle DB into which you are planning to load the XSU is up and java enabled.

3. Execute the appropriate xsuload.xxx file. This will:

4. Load Oracle's XML parser for Java into the database. If the parser is already loaded into the database, you can comment out the line in xsuload.xxx that loads the parser.

5. Load XSU Java classes (that is, load xsu12.jar or xsu111.jar). Load the XSU PL/SQL API (that is, execute the dbmsxsu.sql PL/SQL script)

Requirements for Running XML SQL Utility

There are two versions of the utility, xsu111.jar and xsu12.jar, one compatible for JDK 1.1.x and the other with JDK1.2 respectively.

XML SQL Utility (XSU) is packaged with Oracle8i (8.1.7 and later) and Oracle. XSU is made up of three files:

• $ORACLE_HOME/rdbms/jlib/xsu12.jar -- Contains all the Java classes which make up XSU. xsu12 requires JDK1.2.x and JDBC2.x. This is the XSU version loaded into Oracle.

• $ORACLE_HOME/rdbms/jlib/xsu111.jar -- Contains the same classes as xsu12.jar, except that xsu111 requires JDK1.1.x and JDBC1.x.

• $ORACLE_HOME/rdbms/admin/dbmsxsu.sql -- This is the SQL script that builds the XSU PL/SQL API. xsu12.jar needs to be loaded into the database before dbmsxsu.sql is executed.

By default the Oracle installer installs XSU on your hard drive in the locations specified above. It also loads it into the database.

If during initial installation you choose to not install XSU, you can install it later, but the installation becomes less simple. To install XSU later, first install XSU and its dependent components on your system. You can accomplish this using Oracle Installer. Next perform the following steps:

1. If you have not yet loaded XML Parser for Java in the database, go to $ORACLE_HOME/xdk/lib. Here you will find xmlparserv2.jar that you need to load into the database. To do this, see "Loading JAVA Classes" in the Oracle9i Java Stored Procedures Developer's Guide

2. Go to $ORACLE_HOME/admin and execute catxsu.sql

   Note: XML SQL Utility (XSU) is also available on OTN at: http://otn.oracle.com/tech/xml Check here for XSU updates.

XSU Requirements

Before installing the utility make sure that you choose the right version of the utility depending on your particular needs. For example, if you can only use the JDK1.1.x version, then download the xsu111.jar file. Ensure that you have the JDK and the JDBC drivers correctly downloaded and installed, if not already available.

Extract the XSU Files

After downloading the zip file, simply extract the contents to a directory of your choice, say C:\xml. The files will get expanded in to a subdirectory called xsu111 or xsu112 depending on the version of the utility.

XML SQL Utility (XSU) for Java, Cheat Sheets

The following tables summarize XSU Java API classes and members:

• Table H-2, "XSU Java API: Class OracleXMLQuery"

• Table H-3, "XSU Java API: Class OracleXMLSave"

• Table H-4, "XSU Java API: Class OracleXMLSQLException"

• Table H-5, "XSU Java API: Class OracleXMLSQLNoRowsException"

Table H-2 XSU Java API: Class OracleXMLQuery  

Methods, Parameters, Returns, Constructors,....	Description
Class OracleXMLQuery public class OracleXMLQuery extends java.lang.Object where java.lang.Object is oracle.xml.sql.query.OracleXMLQuery	Generates XML from the database given an SQL query.
Fields	-
DTD public static final int DTD	Specifies that the DTD is to be generated.
ERROR_TAG public static final java.lang.String ERROR_TAG	Specifies the default tag name for the ERROR document.
MAXROWS_ALL public static final int MAXROWS_ALL	Specifies that all rows be included in the result.
MAXROWS_DEFAULT public static final int MAXROWS_DEFAULT	Deprecated since v2.0. Use MAXROWS_ALL instead.
MAXROWS_NONE public static final int MAXROWS_NONE	Deprecated since v2.0. Use 0 instead.
NONE public static final int NONE	Specifies that no DTD is to be generated.
ROW_TAG public static final java.lang.String ROW_TAG	Specifies the default tag name for the ROW elements.
ROWIDATTR_TAG public static final java.lang.String ROWIDATTR_TAG	Specifies the default tag name for the ROW elements.
ROWSET_TAG public static final java.lang.String ROWSET_TAG	Specifies the default tag name for the document.
SCHEMA public static final int SCHEMA	Specifies that no XML schema is to be generated.
SKIPROWS_ALL public static final int SKIPROWS_ALL	Specifies that all rows be skipped in the result.
SKIPROWS_DEFAULT public static final int SKIPROWS_DEFAULT	Deprecated since XSU v2.0. Use 0 instead.
SKIPROWS_NONE public static final int SKIPROWS_NONE	Deprecated since XSU v2.0. Use 0 instead.
Constructors	-
OracleXMLQuery(Connection, ResultSet) public OracleXMLQuery(java.sql.Connection conn, java.sql.ResultSet rset)	Constructor for the OracleXMLQueryObject.
Parameters: conn - database connection, rset - jdbc result set object	-
OracleXMLQuery(Connection, String) public OracleXMLQuery(java.sql.Connection conn, java.lang.String query)	Constructor for the OracleXMLQueryObject.
Parameters: conn - database connection, query - the SQL query string	-
OracleXMLQuery(OracleXMLDataSet) public OracleXMLQuery(oracle.xml.sql.dataset.OracleXMLDataSet dset)	Constructor for the OracleXMLQueryObject.
Parameters: conn - database connection, dset - dataset	-
Methods	-
close() public void close()	Closes any open resource, created by the OracleXML engine. This will not close for instance resultset supplied by the user.
getNumRowsProcessed() public long getNumRowsProcessed()	Returns the number of rows processed.
Returns: Number of rows processed.	-
getXML(OracleXMLDocGen, boolean) public void getXML(oracle.xml.sql.docgen.OracleXMLDocGen doc, boolean withDTD)	Deprecated since XSU v2.0.
getXMLDOM() public org.w3c.dom.Document getXMLDOM()	Transforms the object-relational data, specified in the constructor, into a XML document.
Returns: The DOM representation of the XML document	-
getXMLDOM(boolean) public org.w3c.dom.Document getXMLDOM(boolean withDTD)	Deprecated since XSU 1.2.1. Ue getXMLDOM(int) instead.
getXMLDOM(int) public org.w3c.dom.Document getXMLDOM(int metaType)	Transforms the object-relational data, specified in the constructor, into a XML document. The metaType argument is used to specify the type of XML metadata the XSU is to generate along with the XML. Currently this value is ignored, and no XML metadata is generated.
Parameters: metaType - the type of XML metadata (NONE, SCHEMA)	-
Returns: The string representation of the XML document	-
getXMLDOM(Node) public org.w3c.dom.Document getXMLDOM(org.w3c.dom.Node root)	Transforms the object-relational data, specified in the constructor, into XML. If not NULL, the root argument, is considered the "root" element of the XML doc.
Parameters: root - root node to which to append the new XML, Returns: String representation of the XML document	-
getXMLDOM(Node, int) public org.w3c.dom.Document getXMLDOM(org.w3c.dom.Node root, int metaType)	Transforms the object-relational data, specified in the constructor, into XML. If not NULL, the root argument, is considered the "root" element of the XML doc. MetaType argument is used to specify the type of XML metadata the XSU is to generate along with the XML. Currently this value is ignored, and no XML metadata is generated.
Parameters: root - root node to which to append the new XML, metaType - the type of XML metadata (NONE, SCHEMA)	-
Returns: The string representation of the XML document	-
getXMLMetaData(int, boolean) public java.lang.String getXMLMetaData(int metaType, boolean withVer)	Returns the DTD or XMLSchema for the XML document which would have been generated by a getXML call. The "metaType" parameter specifies the type of XML metadata to be generated. The withVer parameter specifies if version header is to be generated or not.
Parameters: metaType - XML meta data type to generate (NONE or DTD), withVer - generate the version PI ?	-
getXMLSAX(ContentHandler) public void getXMLSAX(org.xml.sax.ContentHandler sax)	Transforms the object-relational data, specified in the constructor, into an XML document.
Parameters: sax - ContentHandler object to be registered	-
getXMLSchema() public org.w3c.dom.Document getXMLSchema()	Generates the XML Schema(s) corresponding to the specified query.
Returns: the XML Schema(s)	-
getXMLString() public java.lang.String getXMLString()	Transforms the object-relational data, specified in the constructor, into a XML document.
Returns: The string representation of the XML document	-
getXMLString(boolean) public java.lang.String getXMLString(boolean withDTD)	Deprecated since XSU v1.2.1. Use getXMLString(int) instead.
getXMLString(int) public java.lang.String getXMLString(int metaType)	Transforms the object-relational data, specified in the constructor, into a XML document. The metaType argument is used to specify the type of XML metadata the XSU is to generate along with the XML. Valid values for the metaType argument are NONE and DTD (static fields of this class).
Parameters: metaType - Tpe of XML metadata (NONE, DTD, or SCHEMA)	-
Returns: String representation of the XML document	-
getXMLString(Node) public java.lang.String getXMLString(org.w3c.dom.Node root)	Transforms the object-relational data, specified in the constructor, into XML. If not NULL, the root argument, is considered the "root" element of the XML document.
Parameters: root - root node to which to append the new XML	-
Returns: String representation of the XML document	-
getXMLString(Node, int) public java.lang.String getXMLString(org.w3c.dom.Node root, int metaType)	Transforms the object-relational data, specified in the constructor, into XML. If not NULL, the root argument, is considered the "root" element of the XML document. MetaType argument specifies the type of XML metadata the XSU is to generate along with the XML. Valid values for the metaType argument are NONE and DTD (static fields of this class). If the root argument is non-null, no DTD is generated even if requested.
Parameters: root - root node to which to append the new XML,	metaType - the type of XML metadata (NONE, DTD, or SCHEMA)
Returns: Sring representation of the XML document	-
keepCursorState(boolean) public void keepCursorState(boolean alive)	Deprecated since v1.2.1. Use keepObjectOpen instead.
keepObjectOpen(boolean) public void keepObjectOpen(boolean alive)	Default behavior for all the getXML functions which DO NOT TAKE in a ResultSet object is to close the ResultSet object and Statement objects at the end of the call. To use the persistant feature, where by calling getXML repeatedly you get the next set of rows, you need to turn off this behavior by calling this function with value true. That is, OracleXMLQuery would not close the ResultSet and Statement objects after the getXML calls. Call close() to explicitly close the cursor state.
Parameters: alive - keep object open ?	-
removeXSLTParam(String) public void removeXSLTParam(java.lang.String name)	Removes the value of a top-level stylesheet parameter. If no stylesheet is registered, this method does not operate.
Parameters: name - parameter name	-
setCollIdAttr(String) public void setCollIdAttr(java.lang.String collIdAttr)	Deprecated since v1.2.1. Please use setCollIdAttrName instead.
setCollIdAttrName(String) public void setCollIdAttrName(java.lang.String attrName)	Sets the name of the id attribute of the collection element's separator tag. Passing null or an empty string for the tag results the row id attribute to be omitted.
Parameters: attrName - attribute name	-
setDataHeader(Reader, String) public void setDataHeader(java.io.Reader header, java.lang.String docTag)	Sets the xml data header. The data header is an XML entity which is appended at the begining of the query-generated xml entity (ie. rowset). The two entities are enclosed by the tag specified via the docTag argument. The last data header specified is the one that is used. Also, passing in null for the header, parameter unsets the data header.
Parameters: header - header, tag - tag used to enclose the data header and the rowset	-
setDateFormat(String) public void setDateFormat(java.lang.String mask)	Sets the format of the generated dates in the XML doc. The syntax of the date format patern (i.e. the date mask), should conform to the requirements of the java.text.SimpleDateFormat class. Setting the mask to null or an empty string, unsets the date mask.
Parameters: mask - the date mask	-
setEncoding(String) public void setEncoding(java.lang.String enc)	Sets the encoding in the XML doc. If null or an empty string are specified as the encoding, then the default characterset is specified in the encoding PI.
Parameters: enc - characterset encoding of the XML document	-
setErrorTag(String) public void setErrorTag(java.lang.String tag)	Sets the tag to be used to enclose the XML error documents.
Parameters: tag - tag name	-
setException(Exception) public void setException(java.lang.Exception e)	Allows the user to pass in an exception, and have the XSU handle it.
Parameters: e - the exception to be processed by the XSU.	-
setMaxRows(int) public void setMaxRows(int rows)	Sets the max number of rows to be converted to XML. By default there is no max set. To explicitly specify no max see MAXROWS_ALL.
Parameters: rows - max number of rows to generate	-
setMetaHeader(Reader) public void setMetaHeader(java.io.Reader header)	Sets the XML meta header. When set, the header is inserted at the begining of the metadata part (DTD or XMLSchema) of each XML document generated by this object. Note that the last meta header specified is the one that is used; furthermore, passing in null for the header, parameter unsets the meta header.
Parameters: header - header	-
setRaiseException(boolean) public void setRaiseException(boolean flag)	Tells the XSU to throw the raised exceptions. If this call is not made or if false is passed to the flag argument, the XSU catches the SQL exceptions and generates an XML document out of the exception's message.
Parameters: flag - throw raised exceptions?	-
setRaiseNoRowsException(boolean) public void setRaiseNoRowsException(boolean flag)	Tells XSU to throw or not to throw an OracleXMLNoRowsException in the case when for one reason or another, the XML doument generated is empty. By default, the exception is not thrown.
Parameters: flag - throw OracleXMLNoRowsException if no data found?	-
setRowIdAttrName(String) public void setRowIdAttrName(java.lang.String attrName)	Sets the name of the id attribute of the row enclosing tag. Passing null or an empty string for the tag results the row id attribute to be omitted.
Parameters: attrName - attribute name	-
setRowIdAttrValue(String) public void setRowIdAttrValue(java.lang.String colName)	Specifies the scalar column whose value is to be assigned to the id attribute of the row enclosing tag. Passing null or an empty string for the colName results the row id attribute being assigned the row count value (i.e. 0, 1, 2 and so on).
Parameters: colName - column whose value is to be assigned to the row id attr	-
setRowIdColumn(String) public void setRowIdColumn(java.lang.String colName)	Deprecated since XSU v1.2.1. Use setRowIdAttrValue instead.
setRowsetTag(String) public void setRowsetTag(java.lang.String tag)	Sets the tag to be used to enclose the xml dataset.
Parameters: tag - tag name	-
setRowTag(String) public void setRowTag(java.lang.String tag)	Sets the tag to be used to enclose the xml element corresponding to a database record.
Parameters: tag - tag name	-
setSkipRows(int) public void setSkipRows(int rows)	Sets the number of rows to skip. By default 0 rows are skipped. To skip all the rows use SKIPROWS_ALL.
Parameters: rows - number of rows to skip	-
setStyleSheet(String) public void setStyleSheet(java.lang.String uri)	Deprecatet since XSU2.0. Use setStylesheetHeader instead.
setStyleSheet(String, String) public void setStyleSheet(java.lang.String uri, java.lang.String type)	Deprecated since XSU2.0. Use setStylesheetHeader instead.
setStylesheetHeader(String) public void setStylesheetHeader(java.lang.String uri)	Sets the stylesheet header (that is, stylesheet processing instructions) in the generated XML doc. Passing null for the uri argument will unset the stylesheet header and the stylesheet type.
Parameters: uri - stylesheet URI	-
setStylesheetHeader(String, String) public void setStylesheetHeader(java.lang.String uri, java.lang.String type)	Sets the stylesheet header (that is, stylesheet processing instructions) in the generated XML document. Passing null for the URI argument will unset the stylesheet header and the stylesheet type.
Parameters: uri - stylesheet URI, type - stylesheet type; defaults to 'text/xsl'	-
setXSLT(Reader, String) public void setXSLT(java.io.Reader stylesheet, java.lang.String ref)	Registers a XSL transform to be applied to generated XML. If a stylesheet was already registered, it gets replaced by the new one. To un-register the stylesheet pass in a null for the stylesheet argument.
Parameters: stylesheet - the stylesheet, ref - URL for include, import and external entities	-
setXSLT(String, String) public void setXSLT(java.lang.String stylesheet, java.lang.String ref)	Registers a XSL transform to be applied to generated XML. If a stylesheet was already registered, it gets replaced by the new one. To un-register the stylesheet pass in a null for the stylesheet argument.
Parameters: stylesheet - the stylesheet URI, ref - URL for include, import and external entities	-
setXSLTParam(String, String) public void setXSLTParam(java.lang.String name, java.lang.String value)	Sets the value of a top-level stylesheet parameter. The parameter value is expected to be a valid XPath expression (String literal values would therefore have to be explicitly quoted). If no stylesheet is registered, this method is not operational.
Parameters: name - parameter name, value - parameter value as an XPATH expression	-
useLowerCaseTagNames() public void useLowerCaseTagNames()	Sets the case to be lower for all tag names. Make this call after all the desired tags have been set.
useNullAttributeIndicator(boolean) public void useNullAttributeIndicator(boolean flag)	Specified weather to use an XML attribute to indicate NULLness, or to do it by omitting the inclusion of the particular entity in the XML document.
Parameters: flag - use attribute to indicate null?	-
useTypeForCollElemTag(boolean) public void useTypeForCollElemTag(boolean flag)	By default the tag name for elements of a collection is the collection's tag name followed by "_item". This method, when called with argument of true, tells XSU to use the collection element's type name as the collection element tag name.
Parameters: flag - use the coll. elem. type as its tag name?	-
useUpperCaseTagNames() public void useUpperCaseTagNames()	Sets the case to be upper for all tag names. Make this call after all the desired tags have been set.

Table H-3 XSU Java API: Class OracleXMLSave  

Methods, Parameters, Returns, Constructors, ...	Description
Class OracleXMLSave public class OracleXMLSave extends java.lang.Object where java.lang.Object is oracle.xml.sql.dml.OracleXMLSave	Supports canonical mapping from XML to object-relational tables or views. It supports inserts, updates and deletes. You first create the class by passing in the table name on which the DML operations need to be done. After that, the user is free to use the insert/update/delete on this table. The useful functions provided in this class help identify the key columns for update or delete and restrict the columns being updated.
Fields	-
DATE_FORMAT public static final java.lang.String DATE_FORMAT	The date format for use in setDateFormat
DEFAULT_BATCH_SIZE public static int DEFAULT_BATCH_SIZE	default insert batch size is 17
Constructors	-
OracleXMLSave(Connection, String) public OracleXMLSave(java.sql.Connection oconn, java.lang.String tabName)	The public constructor for the Save class.
Parameters	oconn - Connection object (connection to the database), tableName - The name of the table that should be updated
Methods	-
cleanLobList() public void cleanLobList()	-
close() public void close()	Closes/deallocates all the context associated with this object.
createURL(String) public java.net.URL createURL(java.lang.String fileName)	Deprecated since XSU2.0. Use the static version of this method instead.
deleteXML(Document) public int deleteXML(org.w3c.dom.Document doc)	Deletes the rows in the table based on the XML document.
Parameters	xmlDoc - The XML document in DOM form
Returns	The number of XML ROW elements processed. See Also: deleteXML(URL)
deleteXML(InputStream) public int deleteXML(java.io.InputStream xmlStream)	Deletes the rows in the table based on the XML document.
Parameters	xmlDoc - The XML document in Stream form
Returns	The number of XML ROW elements processed. See Also: deleteXML(URL)
deleteXML(Reader) public int deleteXML(java.io.Reader xmlStream)	Deletes the rows in the table based on the XML document.
Parameters	xmlDoc - The XML document in Stream form
Returns	The number of XML ROW elements processed.See Also: deleteXML(URL)
deleteXML(String) public int deleteXML(java.lang.String xmlDoc)	Deletes the rows in the table based on the XML document.
Parameters	xmlDoc - The XML document in String form
Returns	The number of XML ROW elements processed.See Also: deleteXML(URL)
deleteXML(URL) public int deleteXML(java.net.URL url)	Deletes rows from a specified table based on the element values in the supplied XML document. By default, the delete processing matches all the element values with the corresponding column names. Each ROW element in the input document is taken as a separate delete statement on the table.
Parameters	url - The URL to the document to use to delete the rows in the table
Returns	Number of XML row elements processed. This may or may not be equal to the number of database rows deleted based on whether the rows selected through the XML document uniquely identified the rows in the table.
finalize() protected void finalize()	Overrides: java.lang.Object.finalize() in class java.lang.Object
getURL(String) public static java.net.URL getURL(java.lang.String target)	Given a file name or a URL it return a URL object. If the argument passed is not in the valid URL format (e.g. http://.. or file://) then this method tried to fix the argument by pre-pending "file://" to the argument. If a null or an empty string are passed to it, null is returned.
Parameters	target - file name or URL string
Returns	the URL object identifiying the target entity
insertXML(Document) public int insertXML(org.w3c.dom.Document doc)	-
insertXML(InputStream) public int insertXML(java.io.InputStream xmlStream)	-
insertXML(Reader) public int insertXML(java.io.Reader xmlStream)	-
insertXML(String) public int insertXML(java.lang.String xmlDoc)	-
insertXML(URL) public int insertXML(java.net.URL url)	Inserts an XML document from a specified URL into the specified table. By default, the insert routine inserts values into the table by matching the element name with the column name and inserts a null value for all elements missing in the input document. By setting the list of columns to insert using the setUpdateColumnList() you can restrict the insert to only insert values into those columns and let the default values for other columns to be inserted. For more details see Chapter 7, "XML SQL Utility (XSU)" and Oracle9i XML Reference
Parameters	url - The URL to the document to use to insert rows into the table
Returns	The number of rows inserted.
removeXSLTParam(String) public void removeXSLTParam(java.lang.String name)	Removes the value of a top-level stylesheet parameter. If no stylesheet is registered, this method is non operational.
Parameters	name - parameter name
setBatchSize(int) public void setBatchSize(int size)	Changes the batch size used during DML operations. When inserting, updating, or deleting, it is better to batch the operations so that the database can execute it once rather than as separate statements. However, more memory is needed to hold all the bind values before the operation is done. Note when batching is used, the commits occur only in terms of batches. So if one of the statement inside a batch fails, the whole batch is rolled back. If this behaviour is unaccepatable, set the batch size to 1. The default batch size is DEFAULT_BATCH_SIZE;
Parameters	size - The batch size to use for all DML
setCommitBatch(int) public void setCommitBatch(int size)	Sets the commit batch size. The commit batch size refers to the number or records inserted after which a commit should follow. If commitBatch is < 1 or the session is in "auto-commit" mode then XSU does not make any explicit commit's. By default the commit-batch size is 0.
Parameters	size - commit batch size
setDateFormat(String) public void setDateFormat(java.lang.String mask)	Describes to XSU the format of the dates in the XML document. By default, OracleXMLSave assumes that the date is in format 'MM/dd/yyyy HH:mm:ss'. You can override this default format by calling this function. The syntax of the date format patern (that is, the date mask), should conform to the requirements of the java.text.SimpleDateFormat class. Setting the mask to null or an empty string, results the use of the default mask -- OracleXMLSave.DATE_FORMAT.
Parameters	mask - the date mask
setIgnoreCase(boolean) public void setIgnoreCase(boolean ignore)	XSU maps XML elements to database columns/attributes based on element names (XML tags). This function tells XSU to do this match case insensitively. This resetting of case may affect metadata caching done when creating the Save object.
Parameters	flag - ignore tag case in the XML doc? 0-false 1-true
setKeyColumnList(String[]) public void setKeyColumnList(java.lang.String[] keyColNames)	Sets the list of columns to be used for identifying a particular row in the database table during update or delete. This call is ignored for the inserts. Key columns must be set before updates can be done. It is optional for deletes. When this key columns is set, then the values from these tags in the XML document is used to identify the database row for update or delete. Currently, there is no way to update the values of the key columns themselves, since there is no way in the XML document to specify that case.
Parameters	keyColNames - The names of the list of columns that are used as keys
setRowTag(String) public void setRowTag(java.lang.String rowTag)	Names the tag used in the XML doc., to enclose the XML elements corresponding to each row value. Setting the value of this to null implies that there is no row tag present and the top level elements of the document correspond to the rows themselves.
Parameters	tag - tag name
setUpdateColumnList(String[]) public void setUpdateColumnList(java.lang.String[] updColNames)	Sets column values to be updated. Only valid for inserts and updates. Ignored for deletes. For inserts, the default is to insert values to all the columns in the table. For updates, the default is to only update the columns corresponding to the tags present in the ROW element of the XML document. When specified, these columns alone are updated in the UPDATE or INSERT statement. All other elements in the document are ignored.
Parameters	updColNames - The string list of columns to be updated
setXSLT(Reader, String) public void setXSLT(java.io.Reader stylesheet, java.lang.String ref)	Registers an XSL transform to be applied to generated XML. If a stylesheet was already registered, it gets replaced by the new one. To un-register the stylesheet pass in a null for the stylesheet argument.
Parameters	stylesheet - the stylesheet, ref - URL for include, import and external entities
setXSLT(String, String) public void setXSLT(java.lang.String stylesheet, java.lang.String ref)	Registers a XSL transform to be applied to generated XML. If a stylesheet was already registered, it gets replaced by the new one. To un-register the stylesheet pass in a null for the stylesheet argument.
Parameters	stylesheet - the stylesheet URI, ref - URL for include, import and external entities
setXSLTParam(String, String) public void setXSLTParam(java.lang.String name, java.lang.String value)	Sets the value of a top-level stylesheet parameter. The parameter value is expected to be a valid XPath expression (note that string literal values would therefore have to be explicitly quoted). If no stylesheet is registered, this method is a no op.
Parameters	name - parameter name, value - parameter value as an XPATH expression
updateXML(Document) public int updateXML(org.w3c.dom.Document doc)	Updates the table given the XML document in a DOM tree form.
Parameters	xmlDoc - The DOM tree form of the XML document
Returns	The number of XML elements processed. See Also: updateXML(URL)
updateXML(InputStream) public int updateXML(java.io.InputStream xmlStream)	Updates the table given the XML document in a stream form.
Parameters	xmlDoc - The stream form of the XML document
Returns	The number of XML elements processed. See Also: updateXML(URL)
updateXML(Reader) public int updateXML(java.io.Reader xmlStream)	Updates the table given the XML document in a stream form.
Parameters	xmlDoc - The stream form of the XML document
Returns	The number of XML elements processed. See Also: updateXML(URL)
updateXML(String) public int updateXML(java.lang.String xmlDoc)	Updates the table given the XML document in a string form.
Parameters	xmlDoc - The string form of the XML document
Returns	The number of XML elements processed. See Also: updateXML(URL)
updateXML(URL) public int updateXML(java.net.URL url)	Updates the columns in a database table, based on the element values in the supplied XML document. The update requires a list of key columns which are used to uniquely identify a row to update in the given table. By default, the update uses the list of key columns and matches the values of the corresponding elements in the XML document to identify a particular row and then updates all the columns in the table for which there is an equivalent element present in the XML document.
Parameters	url - The URL to the document to use to update the table
Returns	The number of XML row elements processed. This may or may not be equal to the number of database rows modified based on whether the rows selected through the XML document uniquely identified the rows in the table.

Table H-4 XSU Java API: Class OracleXMLSQLException  

Constructors and Methods	Description
Class OracleXMLSQLException public class OracleXMLSQLException extends java.lang.RuntimeException	-
Constructors	-
OracleXMLSQLException(Exception) public OracleXMLSQLException(java.lang.Exception e)	-
OracleXMLSQLException(Exception, String) public OracleXMLSQLException(java.lang.Exception e, java.lang.String errorTagName)	-
OracleXMLSQLException(String) public OracleXMLSQLException(java.lang.String message)	-
OracleXMLSQLException(String, Exception) public OracleXMLSQLException(java.lang.String message, java.lang.Exception e)	-
OracleXMLSQLException(String, Exception, String) public OracleXMLSQLException(java.lang.String message, java.lang.Exception e, java.lang.String errorTagName)	-
OracleXMLSQLException(String, int) public OracleXMLSQLException(java.lang.String message, int errorCode)	-
OracleXMLSQLException(String, int, String) public OracleXMLSQLException(java.lang.String message, int errorCode, java.lang.String errorTagName)	-
OracleXMLSQLException(String, String) public OracleXMLSQLException(java.lang.String message, java.lang.String errorTagName)	-
Methods	-
getErrorCode() public int getErrorCode()	-
getParentException() public java.lang.Exception getParentException()	Returns the original exception, if there was one; otherwise, it returns null.
getXMLErrorString() public java.lang.String getXMLErrorString()	Prints the XML error string with the given error tag name.
getXMLSQLErrorString() public java.lang.String getXMLSQLErrorString()	Prints the SQL parameters as well in the error message.
setErrorTag(String) public void setErrorTag(java.lang.String tagName)	Sets the error tag name which is then used by getXMLErrorString and getXMLSQLErrorString, to generate xml error reports.

Table H-5 XSU Java API: Class OracleXMLSQLNoRowsException

Constructors	Description
Class OracleXMLSQLNoRowsException public class OracleXMLSQLNoRowsException extends OracleXMLSQLException	-
Constructors	-
OracleXMLSQLNoRowsException() public OracleXMLSQLNoRowsException()	-
OracleXMLSQLNoRowsException(String) public OracleXMLSQLNoRowsException(java.lang.String errorTag)	-

XML SQL Utility (XSU) for PL/SQL, Cheat Sheets

XML SQL Utility (XSU) for PL/SQL offers the following PL/SQL packages:

• DBMS_XMLQuery -- provides database-to-XML functionality.

• DBMS_XMLSave -- provides XML-to-database functionality.

DBMS_XMLQuery PL/SQL Package

Table H-6 lists DBMS_XMLQuery procedures, functions, and constants.

Table H-6 DBMS_XMLQuery Procedures, Functions, Types, and Constants  

PROCEDURE (Unless Noted Otherwise)	Description
TYPE: ctxType	Type of query context handle. This is the return type of "DBMS_ XMLQuery.newContext()".
CONSTANTS DEFAULT_ROWSETTAG DEFAULT_ERRORTAG DEFAULT_ROWIDATTR DEFAULT_ROWTAG DEFAULT_DATE_FORMAT ALL_ROWS NONE DTD LOWER_CASE UPPER_CASE	Mostly this is the root node tag name,ROWSET ERROR NUM ROW 'MM/dd/yyyy HH:mm:ss' All rows are needed in the output For example, no DTD DTD generation required Use lower case tags User upper case tags
closeContext(ctxType)	Closes/deallocates a particular query context
FUNCTION: getDTD(ctxType, BOOLEAN := false)	-
getDTD(ctxType, CLOB, BOOLEAN := false)	Generates the DTD based on the SQL query used to init.
getExceptionContent(ctxType, NUMBER, VARCHAR2)	-
FUNCTION: getXML(ctxType, NUMBER := NONE)	-
getXML(ctxType, CLOB, NUMBER := NONE)	Generates the XML document.
FUNCTION: newContext(VARCHAR2) --> RETURN -- ctxType	Creates a query context, and it returns the context handle.
FUNCTION: newContext(CLOB) ---> RETURN ---> ctxType	Creates a query context, and it returns the context handle.
propagateOriginalException(ctxType, BOOLEAN)	Tells XSU if an exception is raised, and is being thrown, that XSU should throw the exception raised; rather then, wrapping it with an OracleXMLSQLException.
setBindValue(ctxType, VARCHAR2, VARCHAR2)	Sets a value for a particular bind name.
setCollIdAttrName(ctxType, VARCHAR2	Sets the name of the id attribute of the collection element's separator tag.
setDataHeader(ctxType, CLOB := null, VARCHAR2 := null)	Sets the XML data header.
setDateFormat(ctxType, VARCHAR2)	Sets the format of the generated dates in the XML document.
setErrorTag(ctxType, VARCHAR2)	Sets the tag to be used to enclose the xml error docs.
setMaxRows (ctxType, NUMBER)	Sets the max number of rows to be converted to XML.
setMetaHeader(ctxType, CLOB := null)	Sets the XML meta header.
setRaiseException(ctxType, BOOLEAN)	Tells the XSU to throw the raised exceptions.
setRaiseNoRowsException(ctxType, BOOLEAN)	Tells the XSU to throw or not to throw an OracleXMLNoRowsException in the case when for one reason or another, the XML document generated is empty.
setRowIdAttrName(ctxType, VARCHAR2)	Sets the name of the id attribute of the row enclosing tag.
setRowIdAttrValue(ctxType, VARCHAR2)	Specifies the scalar column whose value is to be assigned to the id attribute of the row enclosing tag.
setRowsetTag(ctxType, VARCHAR2)	Sets the tag to be used to enclose the XML dataset.
setRowTag(ctxType, VARCHAR2)	Sets the tag to be used to enclose the xml element corresponding to a database.
setSkipRows(ctxType, NUMBER)	Sets the number of rows to skip.
setStylesheetHeader(ctxType, VARCHAR2, VARCHAR2 := 'text/xsl')	Sets the stylesheet header
setTagCase(ctxType, NUMBER)	Specified the case of the generated XML tags.
setXSLT(ctxType, VARCHAR2, VARCHAR2 := null)	Registers a stylesheet to be applied to generated XML.
setXSLT(ctxType, CLOB, VARCHAR2 := null)	Registers a stylesheet to be applied to generated XML.
useNullAttributeIndicator(ctxType, BOOLEAN)	Specified weather to use an XML attribute to indicate NULLness, or to do it by omitting the inclusion of the particular entity in the XML document.

DBMS_XMLSave PL/SQL Package

Table H-7 lists DBMS_XMLSave procedures, functions, types, and constants.

Table H-7 DBMS_XMLSave Procedures, Functions, Types, and Constants 

PROCEDURE (Unless Noted Otherwise)	Description
TYPE: ctxType	Type of query context handle. The return type of "DBMS_ XMLSave.newContext()".
CONSTANTS: DEFAULT_ROWTAG DEFAULT_DATE_FORMAT MATCH_CASE IGNORE_CASE	The default tag name for the element cooresponding to db. records. -- ROW Default date mask. -- 'MM/dd/yyyy HH:mm:ss' Used to specify that when mapping XML elements to DB. entities the XSU should be case sensitive Used to specify that when mapping XML elements to DB. entities the XSU should be case insensitive
clearKeyColumnList(ctxType)	Clears the key colubmn list.
clearUpdateColumnList(ctxType)	Clears the update column list.
closeContext(ctxType)	Closes/deallocates a particular save context
FUNCTION: deleteXML(ctxType, CLOB) RETURN NUMBER	Deletes records specified by data from the XML document, from the table specified at the context creation time.
FUNCTION: deleteXML(ctxType, VARCHAR2) RETURN NUMBER	Deletes records specified by data from the XML document, from the table specified at the context creation time.
getExceptionContent(ctxType, NUMBER, VARCHAR2)	Via its arguments, this method returns the thrown exception's error code and error message
FUNCTION: insertXML(ctxType, CLOB) RETURN NUMBER	Inserts the XML document into the table specified at the context creation time.
FUNCTION: insertXML(ctxType, VARCHAR2) RETURN NUMBER .	Inserts the XML document into the table specified at the context creation time
FUNCTION: newContext(targetTable IN VARCHAR2) RETURN ctxType	Creates a save context, and it returns the context handle.
propagateOriginalException(ctxType, BOOLEAN)	Tells the XSU that if an exception is raised, and is being thrown, the XSU should throw the very exception raised; rather then, wrapping it with an OracleXMLSQLException.
setBatchSize(ctxType, NUMBER)	Changes the batch size used during DML operations.
setCommitBatch(ctxType, NUMBER)	Sets the commit batch size.
setDateFormat(ctxType, VARCHAR2)	Describes to the XSU the format of the dates in the XML document.
setIgnoreCase(ctxType, NUMBER)	XSU maps XML elements to the database.
setKeyColumn(ctxType, VARCHAR2)	Adds a column to the "key column list".
setRowTag(ctxType, VARCHAR2)	Names the tag used in the XML document, to enclose the XML elements corresponding to the database.
setUpdateColumn(ctxType, VARCHAR2)	Adds a column to the "update column list".
getExceptionContent(ctxType, NUMBER, VARCHAR2)	Updates the table specified at the context creation time with data from the XML document.
propagateOriginalException(ctxType, BOOLEAN)	Updates the table specified at the context creation time with data from the XML document.
FUNCTION: newContext(targetTable IN VARCHAR2)	-
RETURN ctxType	Creates a save context, and it returns the context handle.
Parameter	targetTable Target table to load XML document to.
Returns:	The context handle.
closeContext(ctxHdl IN ctxType)	Closes/deallocates a particular save context.
Parameter ctxHdl - Context handle	-
setRowTag(ctxHdl IN ctxType, tag IN VARCHAR2)	Names the tag used in the XML document, to enclose the XML elements corresponding to the database records.
Parameters	ctxHdl - Context handle, tag - Tag name
setIgnoreCase(ctxHdl IN ctxType, flag IN NUMBER)	XSU maps XML elements to the database columns/attributes based on element names (XML tags). This function tells XSU to do this match case insensitive.
Parameters	ctxHdl- context handle, flag - ignore tag case in the XML document? 0-false 1-true
setDateFormat(ctxHdl IN ctxType, mask IN VARCHAR2)	Describes to XSU the format of the dates in the XML document. The syntax of the date format pattern (that is, the date mask), should conform to the requirements of the java.text.SimpleDateFormat class. Setting the mask to null or an empty string, results the use of the default mask -- OracleXMLCore.DATE_FORMAT.
Parameters ctxHdl - Context handle, mask - Date mask	-
setBatchSize(ctxHdl IN ctxType, batchSize IN NUMBER);	Changes the batch size used during DML operations. When performing inserts, updates or deletes, it is better to batch the operations so that they get executed in one shot rather than as separate statements. The flip side is that more memory is needed to buffer all the bind values. When batching is used, a commit occurs only after a batch is executed. So if one of the statement inside a batch fails, the whole batch is rolled back. This is a small price to pay considering the performance gain; nevertheless, if this behaviour is unaccepatable, set the batch size to 1. See Also: DEFAULT_BATCH_SIZE
Parameters	ctxHdl - Context handle, batchSize - Batch size
setCommitBatch(ctxHdl IN ctxType, batchSize IN NUMBER);	Sets the commit batch size. Commit batch size refers to the number or records inserted after which a commit should follow. If commitBatch is < 1 or the session is in "auto-commit" mode then XSU does not make any explicit commit's. By default the commit-batch size is 0.
Parameters	ctxHdl - Context handle, ParambatchSize - Commit batch size
setUpdateColumn(ctxHdl IN ctxType, colName IN VARCHAR2);	Adds a column to the "update column list". In inserts, the default is to insert values to all the columns in the table. For updates, the default is to only update the columns corresponding to the tags present in the ROW element of the XML document. When the update column list is specified, the columns making up this list alone will get updated or inserted into.
Parameters	ctxHdl - Context handle, colName - Column to be added to the update column list
clearUpdateColumnList(ctxHdl IN ctxType)	Clears the update column list. See Also: setUpdateColumn
Parameters	ctxHdl - Context handle
setKeyColumn(ctxHdl IN ctxType, colName IN VARCHAR2)	Adds a column to the "key column list". In update or delete, it is the columns in the key column list that make up the WHERE clause of the UPDATE/DELETE statement. The key columns list must be specified before updates can be done; yet, it is only optional for delete operations.
Parameters	ctxHdl - Context handle, colName - Column to be added to the key column list
clearKeyColumnList(ctxHdl IN ctxType)	Clears the key column list. See Also: setKeyColumn
Parameters	ctxHdl - Context handle
FUNCTION insertXML(ctxHdl IN ctxType, xDoc IN VARCHAR2)	-
RETURN NUMBER	Inserts the XML document into the table specified at the context creation time.
Parameters	ctxHdl - Context handle, xDoc - String containing the XML document
Returns	The number of rows inserted.
FUNCTION insertXML(ctxHdl IN ctxType, xDoc IN CLOB)	-
RETURN NUMBER	Inserts the XML document into the table specified at the context creation time.
Parameters	ctxHdl - Context handle, xDocl - String containing the XML document
Returns	The number of rows inserted.
FUNCTION updateXML(ctxHdl IN ctxType, xDoc IN VARCHAR2)	-
RETURN NUMBER	Updates the table specified at the context creation time with data from the XML document.
Parameters	ctxHdl - Context handle, xDoc - String containing the XML document
Returns	The number of rows updated.
FUNCTION updateXML(ctxHdl IN ctxType, xDoc IN CLOB)	-
RETURN NUMBER	Updates the table specified at the context creation time with data from the XML document.
Parameters	ctxHdl - context handle, xDocl - string containing the XML document
Returns	The number of rows updated.
FUNCTION deleteXML(ctxHdl IN ctxType, xDoc IN VARCHAR2)	-
RETURN NUMBER	Deletes records specified by data from the XML document, from the table specified at the context creation time.
Parameters	ctxHdl - context handle, xDoc - string containing the XML document
Returns	The number of rows deleted.
FUNCTION deleteXML(ctxHdl IN ctxType, xDoc IN CLOB)	-
RETURN NUMBER	Deletes records specified by data from the XML document, from the table specified at the context creation time.
Parameters	ctxHdl - context handle, xDocl - string containing the XML document
Returns	The number of rows deleted.
propagateOriginalException(ctxHdl IN ctxType, flag IN BOOLEAN)	Tells XSU that if an exception is raised, and is being thrown, XSU should throw the exception raised; rather then, wrapping it with OracleXMLSQLException.
Parameters	ctxHdl - Context handle, flag - Propagate original exception? 0-false 1-true
getExceptionContent(ctxHdl IN ctxType, errNo OUT NUMBER, errMsg OUT VARCHAR2)	Via its arguments, this returns the thrown exception's error code and error message (that is, SQL error code). This is to get around the fact that the JVM throws an exception on top of whatever exception was raised; PL/SQL is unable to access the original exception.
Parameters	ctxHdl - Context handle, errNo - Error number, errMsg - Error message

See Also: Oracle9i XML Reference Chapter 7, "XML SQL Utility (XSU)" http://otn.oracle.com/tech/xml