Skip Headers

Oracle® Application Developer's Guide - XML 10g (9.0.4) Part Number B12099-01 |
Home | Contents | Index

# 7
XML SQL Utility (XSU)

This chapter contains the following sections:

- What is XML SQL Utility (XSU)?

- XSU Dependencies and Installation

- XML SQL Utility and the Bigger Picture

- SQL-to-XML and XML-to-SQL Mapping Primer

- How XML SQL Utility Works

- Using the XSU Command Line Front End,OracleXML

- XSU Java API

- XSU PL/SQL API

- Advanced XSU Usage Techniques

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

## What is XML SQL Utility (XSU)?

XML has 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 you to do this as follows:

- XSU can transform data retrieved from object-relational database tables or views into XML.

- XSU can extract data from an XML document, and using a canonical mapping, insert the data into appropriate columns or attributes of a table or a view.

- XSU can extract data from an XML document and apply this data to updating or deleting values of the appropriate columns or attributes.

#### Generating XML from the Database

For example, on the XML generation side, when given the query `SELECT * FROM emp`, XSU queries the database and returns the results as 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>

#### Storing XML in the Database

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

#### Accessing XSU Functionality

XML SQL Utility functionality can be accessed in the following ways:

- Through a Java API

- Through a PL/SQL API

- Through a Java command line front end

  See Also: Appendix H, "XML SQL Utility (XSU) Specifications and Cheat Sheets" Oracle9i XML Reference

### XSU Features

XSU can perform the following tasks:

- Generate XML documents from any SQL query. XSU virtually supports all the datatypes supported in the Oracle database server.

- Dynamically generate DTDs (Document Type Definitions).

- During generation, perform simple transformations, such as modifying default tag names for the `ROW` element. You can also register an XSL transformation which is then applied to the generated XML documents as needed.

- Generate XML documents in their string or DOM representations.

- Insert XML into database tables or views. XSU can also update or delete records records from a database object, given an XML document.

- Easily generate complex nested XML documents. XSU can also store them in relational tables by creating object views over the flat tables and querying over these views. Object views can create structured data from existing relational data using Oracle8i and Oracle's object-relational infrastructure.

### XSU Oracle Features

In Oracle, XSU can also perform the following tasks:

- Generates XML Schema given an SQL Query.

- Generates XML as a stream of SAX2 callbacks.

- Supports XML attributes during generation. This provides an easy way to specify that a particular column or group of columns should be mapped to an XML attribute instead of an XML element.

- SQL identifier to XML identifier escaping. Sometimes column names are not valid XML tag names. To avoid this you can either alias all the column names or turn on tag escaping.

  Note: Oracle introduces the `DBMS_XMLGen` PL/SQL supplied package. This package provides the functionality previously available with `DBMS_XMLQuery`. `DBMS_XMLGen` is built into the database code, hence, it provides better performance.

## XSU Dependencies and Installation

### Dependencies

XML SQL Utility (XSU) needs the following components:

- Database connectivity -- JDBC drivers. XSU can work with any JDBC driver but is optimized for Oracle JDBC drivers. Oracle does not make any guarantee or provide support for the XSU running against non-Oracle databases.

- XML Parser -- Oracle XML Parser, Version2. Oracle XML Parser, version 2 is included in Oracle8i and Oracle, and is also available as part of the XSU install downloadable from the Oracle Technology Network (OTN) web site.

### Installation

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 the 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 run `catxsu.sql`

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

## XML SQL Utility and the Bigger Picture

XML SQL Utility (XSU) is written in Java, and 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 Java-enabled Oracle8i or later. Also, 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

  Note: To load and run Java code inside the database you need a Java-enabled Oracle8i or later server.

Figure 7-1 shows the typical architecture for such a system. XML generated from XSU running in 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 in the database or shipped outside through web servers or application servers.

Note: In Figure 7-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 7-1 Running XML SQL Utility in the Database

Text description of the illustration adxml018.gif

### XML SQL Utility 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, 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 could install the XSU in your middle tier and your Java programs could make use of XSU through its Java API.

Figure 7-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 7-2 Running XML SQL Utility in the MIddle Tier

Text description of the illustration adxml019.gif

### 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 you can write Java servlets that use XSU to accomplish their task.

XSQL servlet does just this. 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 your goal, you should probably use the XSQL servlet, as it will spare you from the intricate servlet programming.

See: Chapter 10, "XSQL Pages Publishing Framework" for information about using XSQL Servlet.

#### Figure 7-3 Running XML SQL Utility in a Web Server

Text description of the illustration adxml020.gif

### XML SQL Utility In The Client Tier

XML SQL Utility can be also 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.

## SQL-to-XML and XML-to-SQL Mapping Primer

As described earlier, XML SQL Utility transforms data retrieved from object-relational database tables or views into XML. XSU can also extract data from an XML document, and using a specified mapping, insert the data into appropriate columns or attributes of a table or a view in the database. This section describes the canonical mapping or transformation used to go from SQL to XML or vice versa.

### Default SQL-to-XML Mapping

Consider table `emp`:

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

XSU can generate the following XML document by specifying the query, `select * from emp`:

     <?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 constitute the `<ROWSET>` element. This element is also the root element of the generated XML document.

- The `<ROWSET>` element contains one or more `<ROW>` elements.

- Each of the `<ROW>` elements contain the data from one of the returned database table 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.

- These elements, corresponding to database columns, contain the data from the columns.

#### SQL-to-XML Mapping Against Object-Relational Schema

Next we describe this mapping but against an object-relational schema. Consider the following type, `AddressType`. Its an object type whose attributes are all scalar types and is created as follows:

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

The following type, `EmplyeeType`, is also an object type but it has an `EMPADDR` attribute that is of an object type itself, specifically, `AddressType`. `Employee Type` is created as follows:

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

The following type, `EmployeeListType`, is a collection type whose elements are of the object type, `EmployeeType`. `EmployeeListType` is created as follows:

    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
    )
    NESTED TABLE EMPLIST STORE AS EMPLIST_TABLE;

Assume that valid values are stored in table, `dept`. For the query `select * from dept`, XSU generates 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, that is, `<ROWSET>` contains `<ROW>`s that contain elements corresponding to the columns. As before, the elements corresponding to scalar type columns simply contain the data from the column.

##### Mapping Complex Type Columns to XML

Things get more complex with elements corresponding to a complex type column. For example, `<DEPTADDR>` corresponds to the `DEPTADDR` column which is of object type `ADDRESS`. Consequently, `<DEPTADDR>` contains subelements corresponding to the attributes specified in the type `ADDRESS`. These subelements can contain data or sub-elements of their own, again depending if the attribute they correspond to is of a simple or complex type.

##### Mapping Collections to XML

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:

- The `<ROW>` elements contains a cardinality attribute `num`.

- If a particular column or attribute value is null, then for that row, the corresponding XML element is left out altogether.

- If a top level scalar column name starts with the at sign (@) character, then the particular column is mapped to an XML attribute instead of an XML element.

### Customizing the Generated XML: Mapping SQL to XML

Often, one needs to generate XML with a specific structure. Since the desired structure may differ from the default structure of the generated XML document, it is desirable to have some flexibility in this process. You can customize the structure of a generated XML document using one of the following methods:

- "Source Customization"

- "Mapping Customization"

- "Post-Generation Customization"

#### Source Customization

Source customizations are done by altering the query or database schema. The simplest and most powerful source customizations include the following:

- In the database schema, create an object-relational view that maps to the desired XML document structure.

- In your query:

  - Use cursor subqueries, or cast-multiset constructs to get nesting in the XML document which comes from a flat schema.

  - Alias column/attribute names to get the desired XML element names.

  - Alias top level scalar type columns with identifiers which begin with the at sign (@) to have them map to an XML attribute instead of an XML element . For example, `select empno as "@empno",... from emp`, results in an XML document where the `<ROW>` element has an attribute `EMPNO`.

#### Mapping Customization

XML SQL Utility allows you to modify the mapping it uses to transform SQL data into XML. You can make any of the following SQL to XML mapping changes:

- Change or omit the `<ROWSET>` tag.

- Change or omit the `<ROW>` tag.

- Change or omit the attribute `num`. This is the cardinality attribute of the `<ROW>` element.

- Specify the case for the generated XML element names.

- Specify that XML elements corresponding to elements of a collection, should have a cardinality attribute.

- Specify the format for dates in the XML document.

- Specify that null values in the XML document should be indicated using a nullness attribute, rather then by omission of the element.

#### Post-Generation Customization

Finally, if the desired customization cannot be achieved with the foregoing methods, you can write an XSL transformation and register it with XSU. While there is an XSLT registered with the XSU, XSU can apply XSLT to any XML it generates.

### Default XML-to-SQL Mapping

XML to SQL mapping is just the reverse of the SQL to XML mapping.

See Also: "Default SQL-to-XML Mapping" .

Consider the following differences when mapping from XML to SQL, compared to mapping from SQL to XML:

- When going from XML to SQL, the XML attributes are ignored. Thus, there is really no mapping of XML attributes to SQL.

- When going from SQL to XML, mapping is performed from the resultset created by the SQL query to XML. This way the query can span multiple database tables or views. What gets formed is a single resultset which is then converted into XML. This is not the case when going from XML to SQL, where:

  - To insert one XML document into multiple tables or views, you must create an object-relational view over the target schema.

  - If the view is not updatable, one work around is to use `INSTEAD-OF-INSERT` triggers.

If the XML document does not perfectly map into the target database schema, there are three things you can do:

- Modify the Target. Create an object-relational view over the target schema, and make the view the new target.

- Modify the XML Document. Use XSLT to transform the XML document. The XSLT can be registered with XSU so that the incoming XML is automatically transformed, before any mapping attempts are made. This is the least performant solution.

- Modify XSU's XML-to-SQL Mapping. You can instruct XSU to perform case insensitive matching of the XML elements to database columns or attributes.

  - If not the default (ROW), you can tell XSU to use the name of the element corresponding to a database row.

  - You can instruct XSU on which date format to use when parsing dates in the XML document.

## How XML SQL Utility Works

This section describes how XSU works when performing the following tasks:

- Selecting with XSU

- Inserting with XSU

- Updating with XSU

- Deleting with XSU

### Selecting with XSU

XSU generation is simple. SQL queries are executed and the resultset is retrieved from the database. Metadata about the resultset is aquired and analyzed. Using the mapping described in "Default SQL-to-XML Mapping" , the SQL result set is processed and converted into an XML document.

### Inserting with XSU

To insert the contents of an XML document into a particular table or view, XSU first retrieves the metadata about the target table or view. Based on the metadata, XSU generates an SQL `INSERT` statement. XSU extracts the data out of the XML document and binds it to the appropriate columns or 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 Also: "Default SQL-to-XML Mapping" .

XSU generates the following `INSERT` statement.

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

Next, the XSU parses the XML document, and for each record, it binds the appropriate values to the appropriate columns or attributes, and executes 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'),...)

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 Using XSU (Java API)".

### Updating with XSU

Updates and deletes differ from inserts in that they can affect more than one row in the database table. For inserts, 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, with both updates and deletes, the XML element could match more than one row if the matching columns are not key columns in the table. For updates, you must provide a list of key columns which XSU needs 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 result in the following `UPDATE` statement:

    UPDATE DEPT SET DEPTNAME = ? WHERE DEPTNO = ?

and bind the values,

    DEPTNO <- 100
    DEPTNAME <- SportsDept

For updates, 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 Using XSU (Java API)" .

### Deleting with XSU

For 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 tries to match all the columns given in the document. For an XML document:

    <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, XSU fires off a `DELETE` statement (one per `ROW` element) which looks 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 XSU (Java API)" .

## Using the XSU Command Line Front End,OracleXML

XSU comes with a simple command line front end which gives you quick access to XML generation and insertion. In Oracle, the XSU front end does not publish the update and delete functionalities. The XSU command line options are provided through the Java class, `OracleXML`. Invoke it by calling:

    java OracleXML

This prints the front end usage information. To run the XSU command line front end, first specify where the executable is located. Add the following to your `CLASSPATH`:

- XSU Java library (`xsu12.jar` or`xsu111.jar`)

Also, since XSU depends on Oracle XML Parser and JDBC drivers, make the location of these components known. To do this, the `CLASSPATH` must include the locations of:

- Oracle XML Parser Java library (`xmlparserv2.jar`)

- JDBC library (`classes12.jar` if using`xsu12.jar` or`classes111.jar` if using`xsu111.jar`)

### Generating XML Using the XSU Command Line

For XSU generation capabilities, use the XSU `getXML` parameter. For example, to generate an XML document by querying the `emp` table in the `scott` schema, use:

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

This performs the following tasks:

- Connects to the current default database

- Executes the query `select * from emp`

- Converts the result to XML

- Displays the result

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

### XSU's OracleXML getXML Options

Table 7-1 lists the OracleXML `getXML` options:

##### Table 7-1 XSU's OracleXML getXML Options

getXML Option | Description
--- | ---
-user "<username>/<password>" | Specifies 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>" | Specifies 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>" | Specifies `rowset` tag (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>" | Specifies 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>" | Names 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 `rowID` attribute will tell the XSU to omit the attribute.
-rowIdColumn "<row Id column name>" | Specifies that the value of one of the scalar columns from the query should be used as the value of the `rowID` attribute.
-collectionIdAttr "<collection id attribute name>" | Names the attribute of an 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 `rowID` attribute will tell the XSU to omit the attribute.
-useNullAttrId | Tells the XSU to use the attribute `NULL (TRUE/FALSE)` to indicate the nullness of an element.
-styleSheet "<stylesheet URI>" | Specifies the stylesheet in the XML PI (Processing Instruction).
-stylesheetType "<stylesheet type>" | Specifies the stylesheet type in the XML PI (Processing Instruction).
-errorTag "<error tag name>" | Secifies the error tag -- the tag to enclose error messages which are formated into XML.
-raiseNoRowsException | Tells the XSU to raise an exception if no rows are returned.
-maxRows "<maximum number of rows>" | Specifies the maximum number of rows to be retrieved and converted to XML.
-skipRows "<number of rows to skip>" | Specifies the number of rows to be skipped.
-encoding "<encoding name>" | Specifies the characterset encoding of the generated XML.
-dateFormat "<date format>" | Specifies the date format for the date values in the XML document.
-fileName "<SQL query fileName>" | <sql query> | Specifies the file name which contains the query or specify the query itself.

### Inserting XML Using XSU's Command Line (putXML)

To insert an XML document into the `emp` table in the `scott` schema, use the following syntax:

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

This performs the following tasks:

- Connects to the current database

- Reads the XML document from the given file

- Parses it, matches the tags with column names

- Inserts the values appropriately in to the `emp` table

  Note: The XSU command line front end, `putXML`, currently only publishes XSU `insert` functionality. It may be expanded in future to also publish XSU `update` and`delete` functionality.

### XSU OracleXML putXML Options

Table 7-2 lists the `putXML` options:

:

##### Table 7-2 XSU's OracleXML putXML Options

putXML Options | Description
--- | ---
-user "<username>/<password>" | Specifies 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>" | Specifies the JDBC database connect string. By default the connect string is: "`jdbc:oracle:oci8:@`"):
-batchSize "<batching size>" | Specifies 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>" | Specifies 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>" | Specifies 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>" | Specifies the date format for the date values in the XML document.
-ignoreCase | Makes 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>" | Specifies 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:

- XSU API for XML generation:`oracle.xml.sql.query.OracleXMLQuery`

- XSU API for XML `save`, `insert`, `update`, and `delete`: `oracle.xml.sql.dml.OracleXMLSave`

## Generating XML with XSU's OracleXMLQuery

The `OracleXMLQuery` class makes up the XML generation part of the XSU Java API. Figure 7-4 illustrates the basic steps you need to take when using OracleXMLQuery to generate XML:

1. Create a connection.

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

3. Obtain the result as a DOM tree or XML string.

#### Figure 7-4 Generating XML With XML SQL Utility for Java: Basic Steps

Text description of the illustration adxml032.gif

### Generating XML From SQL Queries Using XSU

The following examples illustrate how XSU can generate an XML document in its DOM or string representation given a SQL query. See Figure 7-5.

#### Figure 7-5 Generating XML With XML SQL Utility

Text description of the illustration adxml014.gif

### XSU Generating XML Example 1: Generating a String From Table emp (Java)

1. Create a connection

   Before generating the XML you must create a connection to the database. The connection can be obtained by supplying the JDBC connect string. First register the Oracle JDBC class and then create the connection, as follows

       // 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 OCI8's 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.

   See Also:: Oracle9i Java Developer's Guide for more details.

   - Connecting With the Thin Driver. Here is an example of connecting using the JDBC thin driver:

         // Create the connection.
         Connection conn =
         DriverManager.getConnection("jdbc:oracle:thin:@dlsun489:1521:ORCL",

                                           "scott","tiger");

     The thin driver requires you to specificy 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. When writing server side Java code, that is, when writing code that will run on 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 this case call the `defaultConnection()` on the `oracle.jdbc.driver.OracleDriver()` class to get the current connection, as follows:

         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 remaining discussion either assumes you are using an OCI8 connection from the client or that you already have a connection object created. Use the appropriate connection creation based on your needs.

2. Creating an OracleXMLQuery Class Instance

   Once you have registered your connection, create an `OracleXMLQuery` class instance by supplying a SQL query to execute as follows:

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

3. Obtain the result as a DOM tree or XML string

   - DOM Object Output. If, instead of a string, you wanted a DOM object, you can simply request a DOM output as follows:

         org.w3c.DOM.Document domDoc = qry.getXMLDOM();

     and use the DOM traversals.

   - XML String Output. You can get an XML string for the result by:

         String xmlString = qry.getXMLString();

Here is a complete listing of the program to extract (generate) the XML string. This program gets the string and prints it out to 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. Execute 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 JDeveloper to compile and run this program. When run, this program prints out the XML file to the screen.

### XSU Generating XML Example 2: Generating DOM From emp table (Java)

DOM (Document Object Model) is a standard defined by the W3C committee. DOM represents an XML document in a parsed tree-like form. Each XML entity becomes a DOM node. Thus XML elements and attributes become DOM nodes while their children become child nodes. To generate a DOM tree from the XML generated by XSU, you can directly request a DOM document from XSU, as it saves the overhead of having to create a string representation of the XML 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 following example illustrates how to generate a DOM tree. The example steps 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, XML SQL Utility (XSU) takes the ResultSet or the query and generates the whole document from all the rows of the query. To obtain 100 rows at a time, you 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 the first five rows of the query and then generate the result.

To obtain the desired results, use the XSU `skipRows` and `maxRows` parameter settings:

- `skipRows` parameter, when set, forces the generation to skip the desired number of rows before starting to generate the result.

- `maxRows` limits the number of rows converted to XML.

For example, if you set `skipRows` to a value of 5 and `maxRows` to a value of 10, then XSU skips the first 5 rows, then generates XML for the next 10 rows.

### Keeping the Object Open For the Duration of the User's Session

In Web scenarios, you may want to keep the query object open for the duration of the user's session. For example, consider 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, request XSU to convert 10 rows at a time and keep the ResultSet state alive, so that the next time you ask XSU for more results, it starts generating from the place the last generation finished. See "XSU Generating XML Example 3. Paginating Results: Generating an XML Page (Java)" .

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

There is also the case when the number of rows, or number of columns in a row are 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 the `keepObjectOpen` function.

### 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, to maintain that state, you need to call the `keepObjectOpen` function to keep the cursor alive. See the following example.

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

This example, writes a simple class that maintains the state and generates the next page each 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

You saw how you can supply a SQL query and get the results as XML. In the last example, you retrieved paginated results. However in Web cases, you may want to retrieve the previous page and not just the next page of results. To provide this scrollable functionality, you can use the Scrollable `ResultSet`. Use the `ResultSet` object to move back and forth within the result set and use XSU to generate the XML each time. The following example illustrates how to do this.

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

This example shows you how to use the JDBC `ResultSet` to generate XML. Note that using the `ResultSet` might be necessary in cases that are not handled directly by XSU, for example, when setting the batch size, binding values, and so on. This example extends the previously defined `pageTest` class to 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 Generating XML Example 5: Generating XML from Procedure Return Values

The `OracleXMLQuery` class provides XML conversion only for query strings or `ResultSets`. But in your application if you have PL/SQL procedures that return REF cursors, how would you do the conversion?

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

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

Consider the following PL/SQL function that 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;
    /

Every time this function is called, it opens a cursor object for the query, `select * from emp` and returns that cursor instance. 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, XSU simply returns a null string. However, it might be desirable to get an exception every time 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 XSU raises an `oracle.xml.sql.OracleXMLSQLNoRowsException`. This is a run time exception and need not be caught unless needed.

### XSU Generating XML Example 6: No Rows Exception (Java)

The following code extends the previous examples to use the exception instead of checking for null strings:

    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 Back in the Database Using XSU OracleXMLSave

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 XSU. The class `oracle.xml.sql.dml.OracleXMLSave` provides this functionality. It has methods to insert XML into tables, update existing tables with the XML document, and delete rows from the table based on XML element values.

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

#### Figure 7-6 Storing XML in the Database Using XML SQL Utility

Text description of the illustration adxml013.gif

Consider an XML document that contains a list of ROW elements, each of which constitutes a separate DML operation, namely, `insert`, `update,` or `delete` on the table or view.

## Insert Processing Using XSU (Java API)

To insert a document into a table or view, simply supply the table or the view name and then the document. XSU parses the document (if a string is given) and then creates an `INSERT` statement into which it binds all the values. By default, XSU inserts values into all the columns of the table or view and an absent element is treated as a `NULL` value. The following example shows you how the XML document generated from the `emp` table, can be stored in the table with relative ease.

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

This example inserts XML values into all columns:

    // This program takes as an argument the file name, or a url to
    // a properly formated XML document and inserts it into the SCOTT.EMP table.
    import java.sql.*;
    import oracle.xml.sql.dml.OracleXMLSave;
    public class testInsert
    {
       public static void main(String argv[])
         throws SQLException
      {
         DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
         Connection conn =
              DriverManager.getConnection("jdbc:oracle:oci8:@","scott","tiger");

         OracleXMLSave sav = new OracleXMLSave(conn, "emp");
         sav.insertXML(sav.getUrl(argv[0]));
         sav.close();
       }
    }

An `INSERT` statement of the form:

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

is generated, and the element tags in the input XML document matching the column names are matched and their values bound.

If you store 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>

to a file and specify the file to the program described above, you would end up with 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 is taken as a null value.

### XSU Inserting XML Example 8: Inserting XML Values into Only Certain Columns

In certain cases, you may not want to insert values into all columns. This may be true when the group of 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 that the salary, manager, department number, and hire date fields are 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`, and so on), those are ignored. Also an `insert` is performed for each `ROW` element that is present in the input. These inserts are batched by default.

## Update Processing Using XSU (Java API)

Now that you know how to insert values into the table from XML documents, see how you can update only certain values. In an XML document, to update the salary of an employee and the department that they work 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 use the XSU to update the values. For updates, you must supply XSU with the list of key column names. These form part of the `WHERE` clause in the `UPDATE` statement. In the `emp` table shown above, employee number (`EMPNO`) column forms the key. Use this for updates.

### XSU Updating XML Example 9: Updating a Table Using the keyColumns (Java)

This example updates `table`, `emp`, 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 are generated. For the first `ROW` element, you generate an `UPDATE` statement to update the `SAL` and`JOB` fields as follows:

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

For the second `ROW` element:

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

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

You may want to specify a 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 in the XML 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, 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 Using XSU (Java API)

When deleting from XML documents, you can set the list of key columns. These columns are used in the `WHERE` clause of the `DELETE` statement. If the key column names are not supplied, then a new `DELETE` statement is created for each `ROW` element of the XML document, where the list of columns in the `WHERE` clause of the `DELETE` statement will match those in the ROW element.

### XSU Deleting XML Example 11: Deleting Operations Per ROW (Java)

Consider this delete example:

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

Using the same XML document shown previously 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 Deleting XML Example 12: Deleting Specified Key Values (Java)

If instead, you want the `DELETE` statement 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 is a single `DELETE` statement of the form:

    DELETE FROM scott.emp WHERE EMPNO=?

This is 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 of XML documents from and to a database. `DBMS_XMLQuery` and `DBMS_XMLSave` are the two packages that reflect the functions in the Java classes - `OracleXMLQuery` and `OracleXMLSave`. Both of 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` and the XSU 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 `select * from emp where empno = :EMPNO_VAR`. Here you are binding 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, and so on.

4. Fetch the XML as a CLOB using the `getXML()` functions. `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 Generating XML Example 13: Generating XML From Simple Queries (PL/SQL)

In this example, you select rows from table `emp`, and obtain an XML document as a CLOB. First get the context handle by passing in a query and then call the `getXMLClob` routine to get the CLOB value. The document is in the same encoding as 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 Generating XML Example 13a: Printing CLOB to Output Buffer

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

    /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 Generating XML Example 14: Changing ROW and ROWSET Tags (PL/SQL)

With the XSU PL/SQL API you can also change the `ROW` and the `ROWSET` tag names. These are the default names placed around each row of the result, and round the whole document, respectively. The procedures, `setRowTagName` and `setRowSetTagName` accomplish this as shown in the following example:

    --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. Each row is separated using the `EMP` tag.

### XSU Generating XML Example 15: Using setMaxRows() and setSkipRows()

The results from the query generation can be paginated by using:

- `setMaxRows` function. This 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.

- `setSkipRows` function. This 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 XML SQL Utility's Java API, call the `keepObjectOpen()` function to ensure that the state is maintained between fetches. The default behavior is to close the state after a fetch. For multiple fetches, you must determine 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 XSU PL/SQL API provides the ability to set stylehseets on the generated XML documents as follows:

- Set the stylesheet header in the result XML. To do this, use `setStylesheetHeader`() procedure, to set the stylesheet header in the result. This simply adds the XML processing instruction to include the stylesheet.

- Apply a stylesheet to the result XML document, before generation. This method is a huge performance win since otherwise the XML document has to be generated as a CLOB, sent to the parser again, and then have the stylesheet applied. XSU generates a DOM document, calls the parser, applies the stylesheet and then generates the result. To apply the stylesheet to the resulting XML document, use the `useStyleSheet`() procedure. This uses the stylesheet to generate the result.

## Binding Values in XSU (PL/SQL)

The XSU PL/SQL API provides the ability to bind values to the SQL statement. The SQL statement can contain named bind variables. The variables must be prefixed with a colon (:) to declare that they are bind variables. To use the bind variable follow these steps:

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.` 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 Generating XML 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 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. For inserts. You can set the list of columns to insert into using the `setUpdateColNames` 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.

## Insert Processing Using XSU (PL/SQL API)

To insert a document into a table or view, simply supply the table or the view name and then the XML document. XSU parses the XML document (if a string is given) and then creates an INSERT statement, into which it binds all the values. By default, XSU 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 Inserting XML Example 16: Inserting Values into All Columns (PL/SQL)

This example creates a procedure, `insProc`, which takes in:

- An XML document as a CLOB

- A table name to put the document into

and then inserts the XML document into the table:

    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');

generates 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 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 is considered a null value.

### XSU Inserting XML Example 17: Inserting Values into 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 that the salary, manager, department number and hiredate fields are 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 `setUpdateColumnName()` procedure repeatedly, passing in a column name to update every time. The column name settings can be cleared using `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,` and so on), those are ignored.

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

## Update Processing Using XSU (PL/SQL API)

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 XSU 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 Updating XML Example 18: Updating an XML Document Using keyColumns

,.......

    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 Updating XML Example 19: Specifying a List of Columns to Update (PL/SQL)

You may 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 Usingh XSU (PL/SQL API)

For deletes, you can set the list of key columns. These columns will be put as part of the `WHERE` clause of the `DELETE` statement. 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 Deleting XML Example 20: Deleting Operations per ROW (PL/SQL)

Consider the `delete` example shown here:

    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 instead you 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 Deleting XML 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. That is, you 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 PL/SQL supplied 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 XSU Usage Techniques

### XSU Exception Handling in Java

#### OracleXMLSQLException class

XSU 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;
       }

}

### XSU Exception Handling in PL/SQL

Here is an XSU 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 Should I Use With XSU to Store XML?

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 (that is, it has a nested structure), you should use an object-relational schema. The XML above will canonically map to such a schema. An appropriate database 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 the following example:

    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. Howevr, this is the least recommended option.

### Can XSU Store XML Data Across Tables?

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 or view. But of course there is a way to store XML with XSU across tables. 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 you can use `INSTEAD OF` triggers over the views to do the inserts.

### Can I Use XML SQL Utility to Load XML Stored in Attributes?

I would like to use XML SQL Utility to load XML where some of the data is stored in attributes; yet, 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 (that is, change your attributes into elements). XML SQL Utility does assume canonical mapping from XML to a database schema. This takes away a bit from the flexibility, forcing you to sometimes resort to XSLT, but at the same time, in the common case, it does not burden you with having to specify a mapping.

### Is XML SQL Utility Case Sensitive? Can I Use ignoreCase?

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, 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 XML SQL Utility raises an error complaining that it could not find a matching column in table, `dual`. So you have two options -- use the correct case or use the `ignoreCase` feature.

### Will XSU Generate Database Schema from a DTD?

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

#### Answer

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

### Can You Provide a Thin Driver Connect String Example for XSU?

I am using the XML SQL Utility 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 database must have an 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?

Does XML SQL Utility commit after it is done i`nserting, deleting`, or `updating`? What happens if an error occurs?

#### Answer

By default XML SQL Utility executes a number of `insert, delete, 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.

Also, by default XML SQL Utility does no explicit commits. If autocommit is on (default for the JDBC connection), then after each batch of statement executions a commit occurs. You 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.

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

### Can You Explain How to Map Table Columns to XML Attributes Using XSU?

#### Question

Can you explain how to map table columns to XML attributes using XSU?

#### Answer

From XML SQL Utility release 2.1.0 you can map a particular column or a group of columns to an XML attribute instead of an XML element. To achieve this, you have to create an alias for the column name, and prepend the at sign (@) to the name of this alias. For example :

    * Create a file called select.sql with the following content :
       SELECT empno "@EMPNO", ename, job, hiredate
       FROM emp
       ORDER BY empno

     * Call the XML SQL Utility :
       java OracleXML getXML -user "scott/tiger" \
               -conn "jdbc:oracle:thin:@myhost:1521:ORCL" \
               -fileName "select.sql"

     * As a result, the XML document will look like :
         <?xml version = '1.0'?>
         <ROWSET>
            <ROW num="1" EMPNO="7369">
               <ENAME>SMITH</ENAME>
               <JOB>CLERK</JOB>
               <HIREDATE>12/17/1980 0:0:0</HIREDATE>
            </ROW>
            <ROW num="2" EMPNO="7499">
               <ENAME>ALLEN</ENAME>
               <JOB>SALESMAN</JOB>
               <HIREDATE>2/20/1981 0:0:0</HIREDATE>
            </ROW>
         </ROWSET>

Note: All attributes must appear before any non-attribute.

Since the XML document is created in a streamed manner, the query :

    SELECT ename, empno "@EMPNO", ...

would not generate the expected result. It is currently not possible to load XML data stored in attributes. You will still need to use an XSLT transformation to change the attributes into elements. XML SQL Utility assumes canonical mapping from XML to a database schema.

### How Can I Use XMLGEN.insertXML with LOBs?

I am using the following:

- OS: SOLARIS 7

- DB: ORACLE 815

and trying to use the `insertXML` procedure from XSU. I have little experience with using LOBS. What is the problem in my script?

I have a table `lob_temp`:

     SQL> desc lob_temp
     Name Null? Type
     ----------------- -------- ------------------  ----------
     CHUNK CLOB

     SQL> set long 100000
     SQL> select * from lob_temp;

     CHUNK
     -----------------------------------------  ----------
     <DOCID> 91739.1 </DOCID>

<SUBJECT> MTS: ORA-29855, DRG-50704, ORA-12154: on create index using Intermedia

     </SUBJECT>
     <TYPE> PROBLEM </TYPE>
     <CONTENT_TYPE> TEXT/PLAIN </CONTENT_TYPE>
     <STATUS> PUBLISHED </STATUS>
     <CREATION_DATE> 14-DEC-1999 </CREATION_DATE>
     <LAST_REVISION_DATE> 05-JUN-2000 </LAST_REVISION_DATE>
     <LANGUAGE> USAENG </LANGUAGE>

I have another table where I need to insert data from `lob_temp`:

     SQL> desc metalink_doc
     Name Null? Type
     ---------------- -------- ------------------ ----------
     DOCID VARCHAR2(10)
     SUBJECT VARCHAR2(100)
     TYPE VARCHAR2(20)
     CONTENT_TYPE VARCHAR2(20)
     STATUS VARCHAR2(20)
     CREATION_DATE DATE
     LAST_REVISION_DATE DATE
     LANGUAGE VARCHAR2(10)

This is the script. It is supposed to read data from `lob_temp` and then insert the data, extracted from the XML document, to table `metalink_doc`:

     declare
     xmlstr clob := null;
     amount integer := 255;
     position integer := 1;
     charstring varchar2(255);
     finalstr varchar2(4000) := null;
     ignore_case constant number := 0;
     default_date_format constant varchar2(21) := 'DD-MON-YYYY';
     default_rowtag constant varchar2(10) := 'MDOC_DATA';
     len integer;
     insrow integer;
     begin
     select chunk into xmlstr from lob_temp;
     dbms_lob.open(xmlstr,dbms_lob.lob_readonly);
     len := dbms_lob.getlength(xmlstr);
     while position < len loop
     dbms_lob.read(xmlstr,amount,position,charstring);
     if finalstr is not null then
     finalstr := finalstr||charstring;
     else
     finalstr := charstring;
     end if;
     position := position + amount;
     end loop;
     insrow := xmlgen.insertXML('metalink_doc',finalstr);
     dbms_output.put_line(insrow);
     dbms_lob.close(xmlstr);
     exception
     when others then
     dbms_lob.close(xmlstr);
     dbms_lob.freetemporary(xmlstr);
     end;
     /

This is the error received:

    ERROR at line 1:
    ORA-22275: invalid LOB locator specified
    ORA-06512: at "SYS.DBMS_LOB", line 485
    ORA-06512: at line 31
    ORA-29532: Java call terminated by uncaught Java exception:
    oracle.xml.sql.OracleXMLSQLException: Expected 'EOF'.

The user I am using owns both tables, and all objects created when I ran `oraclexmlsqlload.csh`.

#### Answer

You need to have `<ROWSET>` and `<ROW>` tags to insert XML document into a table. I modified your procedure as below. There is a problem when parsing the `DATE` format, hence I used `VARCHAR2`:

    drop table lob_temp;
     create table lob_temp (chunk clob);
     insert into lob_temp values ('
     <ROWSET>
     <ROW>
     <DOCID> 91739.1 </DOCID>
     <SUBJECT> MTS: ORA-29855, DRG-50704, ORA-12154: on create index using
    Intermedia </SUBJECT>
     <TYPE> PROBLEM </TYPE>
     <CONTENT_TYPE> TEXT/PLAIN </CONTENT_TYPE>
     <STATUS> PUBLISHED </STATUS>
     <CREATION_DATE> 14-DEC-1999 </CREATION_DATE>
     <LAST_REVISION_DATE> 05-JUN-2000 </LAST_REVISION_DATE>
     <LANGUAGE> USAENG </LANGUAGE>
     </ROW>
     </ROWSET>
     ');

     drop table metalink_doc;
     create table metalink_doc (
     DOCID VARCHAR2(10),
     SUBJECT VARCHAR2(100),
     TYPE VARCHAR2(20),
     CONTENT_TYPE VARCHAR2(20),
     STATUS VARCHAR2(20),
     CREATION_DATE VARCHAR2(50),
     LAST_REVISION_DATE varchar2(50),
     LANGUAGE VARCHAR2(10)
     );

     create or replace procedure prtest as
     xmlstr clob := null;
     amount integer := 255;
     position integer := 1;
     charstring varchar2(255);
     finalstr varchar2(4000) := null;
     ignore_case constant number := 0;
     default_date_format constant varchar2(21) := 'DD-MON-YYYY';
     default_rowtag constant varchar2(10) := 'MDOC_DATA';
     len integer;
     insrow integer;
     begin

     select chunk into xmlstr from lob_temp;
     dbms_lob.open(xmlstr,dbms_lob.lob_readonly);
     len := dbms_lob.getlength(xmlstr);

     while position < len loop
     dbms_lob.read(xmlstr,amount,position,charstring);
     if finalstr is not null then
     finalstr := finalstr||charstring;
     else
     finalstr := charstring;
     end if;
     position := position + amount;
     end loop;

     insrow := xmlgen.insertXML('metalink_doc',finalstr);
     dbms_output.put_line(insrow);

     IF DBMS_LOB.ISOPEN(xmlstr) = 1 THEN
     dbms_lob.close(xmlstr);
     END IF;

     exception
     when others then
     IF DBMS_LOB.ISOPEN(xmlstr)=1 THEN
     dbms_lob.close(xmlstr);
     END IF;
     end;
     /
     show err

#### Response Comment

Its working! Thank you!

| Copyright © 2001, 2003 Oracle Corporation. All Rights Reserved. |
Home | Contents | Index