8
Package oracle.xml.sql.dml

This chapter documents the class contained in package oracle.xml.sql.dml, which handles data manipulation and modification for XML SQL Utility for Java (XSU). XSU is part of the Oracle XDK for Java. XML SQL Utility for Java generates and stores XML data to and from the database from SQL queries or result sets or tables. It achieves data transformation by mapping canonically any SQL query result to XML, and vice versa.

This chapter contains these sections:

• Package oracle.xml.sql.dml Description
• OracleXMLSave Class

---

Package oracle.xml.sql.dml Description

Package oracle.xml.sql.dml implements data manipulation and modification functions for Oracle XDK for Java. (DML is for Data Manipulation/Modification Language.) The methods for DML operations are provided in the OracleXMLSave class contained in this package. The OracleXMLSave class supports canonical mapping from XML to object-relational tables or views.

---

OracleXMLSave Class

Description

OracleXMLSave class supports canonical mapping from XML to object-relational tables or views. It supports inserts, updates and deletes. The user first creates the class by passing in the table name on which these DML operations need to be done. After that, the user is free to use the insert/update/delete on this table.

Many useful functions are provided in this class to help in identifying the key columns for update or delete and to restrict the columns being updated.

Syntax

public class OracleXMLSave extends java.lang.Object
 
java.lang.Object
  |
  +--oracle.xml.sql.dml.OracleXMLSave

---

Fields

Table 8-1  Summary of Fields of OracleXMLSave

Field	Syntax	Description
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
xDocIsEsc	public boolean xDocIsEsc	Indicates whether or not the xml doc has undergone SQL to XML escaping

---

Methods

Table 8-2  Summary of Methods of OracleXMLSave

Method	Description
OracleXMLSave()	The public constructor for the Save class.
close()	It closes/deallocates all the context associated with this object.
deleteXML()	Deletes the rows in the table based on the XML document.
getURL()	Return a URL object given a file name or a URL.
insertXML()	Inserts an XML document into the specified table.
removeXSLTParam()	Removes the value of a top-level stylesheet parameter.
setBatchSize()	Changes the batch size used during DML operations.
setCommitBatch()	Sets the commit batch size.
setDateFormat()	Describes to the XSU the format of the dates in the XML document.
setIgnoreCase()	Instructs the XSU to perform a case-insensitive match of XML elements to database columns or attributes.
setKeyColumnList()	Sets the list of columns to be used for identifying a particular row in the database table during update or delete.
setPreserveWhitespace()	Instructs the XSU whether to preserve whitespaces.
setRowTag()	Names the tag used in the XML doc. to enclose the XML elements corresponding to each row value.
setSQLToXMLNameEscaping()	This turns on or off escaping of XML tags when an SQL object name would not make a valid XML identifier.
setUpdateColumnList()	Set the column values to be updated.
setXSLT()	Registers a XSL transform to be applied to generated XML.
setXSLTParam()	Sets the value of a top-level stylesheet parameter.
updateXML()	Updates the table given the XML document.

OracleXMLSave()

Description

The public constructor for the OracleXMLSave class.

Syntax

public  OracleXMLSave(java.sql.Connection oconn,

            java.lang.String tabName;

Parameters

oconn	Connection object (connection to the database)
tableName	The name of the table that should be updated

close()

Description

Closes/deallocates all the context associated with this object.

Syntax

public void close();

deleteXML()

Description

Deletes the rows in the table based on the XML document. Returns the 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.

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. By using the setKeyColumnList(), the list of columns that must be matched to identify the row to be deleted is set, and other elements are ignored. This is an efficient method for deleting more than one row in the table if matching is employed (since the delete statement is cached). Otherwise, a new delete statement has to be created for each ROW element in the input document. The syntax options are described in the table here.

Syntax	Description
public int deleteXML( org.w3c.dom.Document doc);	XML document is in DOM form.
public int deleteXML( java.io.InputStream xmlStream);	XML document is in Stream form.
public int deleteXML( java.io.Reader xmlReader);	XML document is in Reader form.
public int deleteXML( java.lang.String xmlDoc);	XML document is in String form.
public int deleteXML ( java.net.URL url);	XML document is accessed via the URL.

Parameters

doc	The XML document in DOM form.
xmlStream	The XML document in Stream form.
xmlReader	The XML document in Reader form.
xmlDoc	The XML document in String form.
url	The URL to the document to use to delete the rows in the table.

getURL()

Description

Returns a URL object identifying the target entity, given a file name or a URL. If the argument passed is not in a valid URL format, such as "http://..." or "file://...", then this method attempts to correct the argument by pre-pending "file://". If a NULL or an empty string are passed to it, NULL is returned.

Syntax

public static java.net.URL getURL( java.lang.String target);

Parameters

target

File name or URL string.

insertXML()

Description

Inserts an XML document into the specified table. Returns the number of rows inserted.

• Inserts the values into the table by matching the element name with the column name, and inserts a NULL value for all elements that are missing in the input document. By using the setUpdateColumnList(), no NULL values would be inserted for the rest of the columns; instead, default values would be used.
• To set the list of all key column, use setKeyColumnList().
• To set the list of columns to update, use setUpdateColumnList().

The options are described in the following table.

Syntax	Description
public int insertXML( org.w3c.dom.Document doc);	Inserts an XML document from a DOM.
public int insertXML( java.io.InputStream xmlStream);	Inserts an XML document from an InputStream.
public int insertXML( java.io.Reader xmlStream);	Inserts an XML document from a Reader.
public int insertXML( java.lang.String xmlDoc);	Inserts an XML document from a String.
public int insertXML( java.net.URL url);	Inserts an XML document from a URL.

Parameters

doc	DOM for inserting rows into the table.
xmlStream	Stream of data used for inserting rows into the table.
xmlDOC	String used for inserting rows into the table.
url	The URL to the document used for inserting rows into the table.

removeXSLTParam()

Description

Removes the value of a top-level stylesheet parameter. If no stylesheet is registered, this method is a no op.

Syntax

public void removeXSLTParam( java.lang.String name);

Parameters

name	Parameter name

setBatchSize()

Description

Changes the batch size used during DML operations. When performing inserts, updates or deletes, it is recommended to batch the operations to minimize I/O cycles; however, this requires more cache for storing the bind values while the operations are executing. When batching is used, the commits occur only in terms of batches. If a single statement inside a batch fails, the entire batch is rolled back. If this behavior is undesirable, set batch size to 1. The default batch size is DEFAULT_BATCH_SIZE.

Syntax

public void setBatchSize(int size);

Parameters

size	The batch size to use for all DML.

setCommitBatch()

Description

Sets the commit batch size, which refers to the number of records inserted after which a commit must follow. If size < 1, or the session is in "auto-commit" mode, the XSU does not make any explicit commits. Default commit-batch size is 0.

Syntax

public void setCommitBatch( int size);

Parameters

size	Commit batch size.

setDateFormat()

Description

Describes to the 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 pattern (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, causes the use of the default mask -- OracleXMLSave.DATE_FORMAT.

Syntax

public void setDateFormat( java.lang.String mask);

Parameters

mask	The date mask.

setIgnoreCase()

Description

The XSU performs mapping of XML elements to database columns or attributes based on the element names (XML tags). This function instructs the XSU to perform a case-insensitive match. This may affect the metadata caching performed when creating the Save object.

Syntax

public void setIgnoreCase(boolean ignore);

Parameters

flag	Should the tag case in the XML doc be ignored?

setKeyColumnList()

Description

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 insert case. The 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.

Syntax

public void setKeyColumnList( java.lang.String[] keyColNames);

Parameters

keyColNames

The names of the list of columns that are used as keys.

setPreserveWhitespace()

Description

Instructs the XSU whether to preserve whitespaces.

Syntax

public void setPreserveWhitespace( boolean flag);

Parameters

flag	Should the whitespaces be preserved?

setRowTag()

Description

Names the tag used in the XML doc so 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.

Syntax

public void setRowTag( java.lang.String rowTag);

Parameters

tag

Tag name.

setSQLToXMLNameEscaping()

Description

This turns on or off escaping of XML tags when the SQL object name, which is mapped to a XML identifier, is not a valid XML identifier.

Syntax

public void setSQLToXMLNameEscaping( boolean flag);

Parameters

flag	Should the SQL to XML escaping be turned on?

setUpdateColumnList()

Description

Set the column values to be updated. Applies to inserts and updates, not deletes.

• In case of insert, the default is to insert values to all the columns in the table.
• In case of 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 will get updated in the update or insert statement. All other elements in the document will be ignored.

Syntax

public void setUpdateColumnList(java.lang.String[] updColNames);

Parameters

updColNmaes

The string list of columns to be updated.

setXSLT()

Description

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. The options are described in the following table.

Syntax	Description
public void setXSLT( java.io.Reader stylesheet, java.lang.String ref);	The stylesheet parameter is passed in as the data.
public void setXSLT( java.lang.String stylesheet, java.lang.String ref);	The stylesheet parameter is passed in as a URI to the document.

Parameters

stylesheet	The stylesheet URI.
ref	URL for include, import and external entities.

setXSLTParam()

Description

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.

Syntax

public void setXSLTParam(java.lang.String name,

                    java.lang.String value);

Parameters

name	Parameter name.
value	Parameter value as an XPATH expression.

updateXML()

Description

Updates the table given the XML document. Returns the number of XML elements processed. This may or may not be equal to the number of database rows modified, depending on whether the rows selected through the XML document uniquely identify the rows in the table.

• 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, subsequently updating all the columns in the table for which there is an equivalent element present in the XML document. Each ROW element present in the input document is treated as a separate update to the table.
• A a list of columns to update can be supplied to update only desired columns and ignore any other elements present in the XML document. This is a very efficient method, because if there are more than one row present in the input XML document, the update statement itself is cached and batched.
• To set the list of all key column, use setKeyColumnList().
• To set the list of columns to update, use setUpdateColumnList().

The options are described in the following table.

Syntax	Description
public int updateXML( org.w3c.dom.Document doc);	Updates the table given the XML document in a DOM tree form.
public int updateXML( java.io.InputStream xmlStream);	Updates the table given the XML document in a stream form.
public int updateXML( java.io.Reader xmlStream);	Updates the table given the XML document in a stream form.
public int updateXML( java.lang.String xmlDoc);	Updates the table given the XML document in a string form.
public int updateXML( java.net.URL url);	Updates the columns in a database table, based on the element values in the supplied XML document.

Parameters

doc	The DOM tree form of the XML document
xmlStream	The stream form of the XML document
xmlDoc	The string form of the XML document
url	The URL to the document to use to update the table