11 Using the XML SQL Utility (XSU)

Generating XML with the XSU Java API: Basic Process

The OracleXMLQuery class makes up the XML generation part of the XSU Java API. Figure 11-1 illustrates the basic process for generating XML with XSU.

The basic steps in Figure 11-1 are as follows:

Figure 11-1 Generating XML with XSU

Description of "Figure 11-1 Generating XML with XSU"

1. Create a JDBC connection to the database. Normally, you establish a connection with the DriverManager class, which manages a set of JDBC drivers. After the JDBC drivers are loaded, call getConnection(). When it finds the right driver, this method returns a Connection object that represents a database session. All SQL statements are executed within the context of this session.

   You have the following options:

   • Create the connection with the JDBC OCI driver. The following code fragment illustrates this technique:

     // import the Oracle driver class
     import oracle.jdbc.*;
     // load the Oracle JDBC driver
     DriverManager.registerDriver(new oracle.jdbc.OracleDriver());     
     // create the connection
     Connection conn =
        DriverManager.getConnection("jdbc:oracle:oci:@","hr","hr");
     

     The preceding example uses the default connection for the JDBC OCI driver.

   • Create the connection with the JDBC thin driver. The thin driver is written in pure Java and can be called from any Java program. The following code fragment illustrates this technique:

     Connection conn =        
        DriverManager.getConnection("jdbc:oracle:thin:@dlsun489:1521:ORCL",
          "hr","hr");
     

     The thin driver requires the host name (dlsun489), port number (1521), and the Oracle SID (ORCL). The database must have an active TCP/IP listener.

   • Use default connection used by the server-side internal JDBC driver. This driver runs within a default session and default transaction context. You are already connected to the database; your SQL operations are part of the default transaction. Thus, you do not need to register the driver. Create the Connection object as follows:

     Connection conn = new oracle.jdbc.OracleDriver().defaultConnection ();
     

   Note:

   OracleXMLDataSetExtJdbc is used only for Oracle JDBC, whereas OracleXMLDataSetGenJdbc is used for non-Oracle JDBC. These classes are in the oracle.xml.sql.dataset package.

2. Create an XML query object and assign it a SQL query. You create an OracleXMLQuery Class instance by passing a SQL query to the constructor, as shown in the following example:

   OracleXMLQuery qry = new OracleXMLQuery (conn, "SELECT * from EMPLOYEES");
   

3. Configure the XML query object by invoking OracleXMLQuery methods. The following example specifies that only 20 rows should be included in the result set:

   xmlQry.setMaxRows(20); 
   

4. Return a DOM object or string by invoking OracleXMLQuery methods. For example, obtain a DOM object as follows:

   XMLDocument domDoc = (XMLDocument)qry.getXMLDOM();
   

   Obtain a string object as follows:

   String xmlString = qry.getXMLString();
   

5. Perform additional processing on the string or DOM as needed.

   See Also:

   • Oracle Database Java Developer's Guide to learn about Oracle JDBC

   • Oracle Database XML Java API Reference to learn about OracleXMLQuery methods

Performing DML with the XSU Java API: Basic Process

Use the OracleXMLSave class to insert, update, and delete XML in the database. Figure 11-2 illustrates the basic process.

Figure 11-2 Storing XML in the Database Using XSU

Description of "Figure 11-2 Storing XML in the Database Using XSU"

The basic steps in Figure 11-2 are as follows:

1. Create a JDBC connection to the database. This step is identical to the first step described in "Generating XML with the XSU Java API: Basic Process".

2. Create an XML save object and assign it a table on which to perform DML. Pass a table or view name to the constructor, as shown in the following example:

   OracleXMLSave sav = new OracleXMLSave(conn, "employees");
   

3. Specify the primary key columns. For example, the following code specifies that employee_id is the key column:

   String [] keyColNames = new String[1];
   keyColNames[0] = "EMPLOYEE_ID";
   sav.setKeyColumnList(keyColNames);
   

4. Configure the XML save object by invoking OracleXMLSave methods. The following example specifies an update of the salary and job_id columns:

   String[] updateColNames = new String[2];
   updateColNames[0] = "SALARY";
   updateColNames[1] = "JOB_ID";
   sav.setUpdateColumnList(updateColNames); // set the columns to update
   

5. Invoke the insertXML(), updateXML(), or deleteXML() methods on the OracleXMLSave object. The following example illustrates an update:

   // Assume that the user passes in this XML document as the first argument
   sav.updateXML(sav.getURL(argv[0]));
   

   When performing the DML, XSU performs the following tasks:

   1. Parses the input XML document.

   2. Matches element names to column names in the target table or view.

   3. Converts the elements to SQL types and binds them to the appropriate statement.

6. Close the OracleXMLSave object and deallocate all contexts associated with it, as shown in the following example:

   sav.close();
   

   See Also:

   • Oracle Database Java Developer's Guide to learn about JDBC

   • Oracle Database XML Java API Reference to learn about OracleXMLSave

Generating XML with the XSU PL/SQL API: Basic Process

The XSU PL/SQL API reflects the Java API in the generation and storage of XML documents from and to a database. DBMS_XMLQuery is the PL/SQL package that reflects the methods in the OracleXMLQuery Java class. This package has a context handle associated with it. Create a context by calling one of the constructor-like functions to get the handle and then use the handle in all subsequent calls.

XSU supports the XMLType datatype. Using XSU with XMLType is useful if, for example, you have XMLType columns in objects or tables.

Generating XML results in a CLOB that contains the XML document. To use DBMS_XMLQuery and the XSU generation engine, follow these basic steps:

1. Declare a variable for the XML query context and a variable for the generated XML. For example:

   v_queryCtx  DBMS_XMLQuery.ctxType;
   v_result    CLOB;
   

2. Obtain a context handle by calling the DBMS_XMLQuery.newContext function and supplying it the query, either as a CLOB or a VARCHAR2. The following example registers a query to select the rows from the employees table with the WHERE clause containing the bind variables :EMPLOYEE_ID and :FIRST_NAME:

   v_queryCtx = DBMS_XMLQuery.newContext('SELECT * FROM employees 
                      WHERE employee_id=:EMPLOYEE_ID AND first_name=:FIRST_NAME');
   

3. Bind values to the query. The binds work by binding a name to the position. clearBindValues clears all the bind variables, whereas setBindValue sets a single bind variable with a string value. For example, bind the employee_id and first_name values as shown:

   DBMS_XMLQuery.setBindValue(v_queryCtx,'EMPLOYEE_ID',20);
   DBMS_XMLQuery.setBindValue(v_queryCtx,'FIRST_NAME','John');
   

4. Configure the query context. Set optional arguments such as the ROW tag name, the ROWSET tag name, or the number of rows to fetch, and so on. The following example specifies changes the default ROWSET element name to EMPSET:

   DBMS_XMLQuery.setRowSetTag(v_queryCtx,'EMPSET');
   

5. Fetch the results. You can obtain the XML as a CLOB with the getXML function, which generates XML with or without a DTD or XML schema. The following example applies bind values to the statement and gets the result corresponding to the predicate employee_id = 20 and first_name = 'John':

   v_result := DBMS_XMLQuery.getXML(v_queryCtx);
   

6. Process the results of the XML generation. For example, suppose that your program declared the following variables:

   v_xmlstr VARCHAR2(32767);
   v_line   VARCHAR2(2000);
   

   You can print the CLOB stored in v_result as follows:

   v_xmlstr := DBMS_LOB.SUBSTR(v_result,32767);
   LOOP
     EXIT WHEN v_xmlstr IS NULL;
     v_line := substr(v_xmlstr,1,INSTR(v_xmlstr,CHR(10))-1);
     DBMS_OUTPUT.PUT_LINE('| ' || v_line);
     v_xmlstr := SUBSTR(v_xmlstr,INSTR(v_xmlstr,CHR(10))+1);
   END LOOP;
   

7. Close the context. For example:

   DBMS_XMLQuery.closeContext(v_queryCtx);
   

Performing DML with the PL/SQL API: Basic Process

DBMS_XMLSave is the PL/SQL package that reflects the methods in the OracleXMLSave Java class. This package has a context handle associated with it. Create a context by calling one of the constructor-like functions to get the handle and then use the handle in all subsequent calls.

To use DBMS_XMLSave, follow these basic steps:

1. Declare a variable for the XML save context and a variable for the number of rows touched by the DML. For example:

   savCtx DBMS_XMLSave.ctxType;
   v_rows   NUMBER;
   

2. Create a context handle by calling the DBMS_XMLSave.newContext function and supply it the table name to use for the DML operations.

   savCtx  := DBMS_XMLSave.newContext('hr.employees');
   

3. Set options based on the type of DML that you want to perform.

   For inserts you can set the list of columns to insert into the setUpdateColumn function. The default is to insert values into all columns. The following example sets five columns in the employees table:

   DBMS_XMLSave.setUpdateColumn(savCtx,'EMPLOYEE_ID'); 
   DBMS_XMLSave.setUpdateColumn(savCtx,'LAST_NAME');
   DBMS_XMLSave.setUpdateColumn(savCtx,'EMAIL');
   DBMS_XMLSave.setUpdatecolumn(savCtx,'JOB_ID');
   DBMS_XMLSave.setUpdateColumn(savCtx,'HIRE_DATE');
   

   For updates you must supply the list of key columns. Optionally, you can then supply the list of columns for update. 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 example:

   DBMS_XMLSave.setKeyColumn(savCtx,'employee_id'); -- set key column
   -- set list of columns to update.
   DBMS_XMLSave.setUpdateColumn(savCtx,'salary');
   DBMS_XMLSave.setUpdateColumn(savCtx,'job_id');
   

   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, set the list of key columns. In this case only those tag values whose tag names match these columns are used to identify the rows to delete (in effect used in the WHERE clause of the DELETE statement). For example:

   DBMS_XMLSave.setKeyColumn(savCtx,'EMPLOYEE_ID');
   

4. Supply a context and XML document to the insertXML, updateXML, or deleteXML functions. For example:

   v_rows := DBMS_XMLSave.deleteXML(savCtx,xmlDoc);
   

5. Repeat the DML any number of times if needed.

6. Close the context. For example:

   DBMS_XMLSave.closeContext(savCtx);
   

For a model use the Java examples described in "Programming with the XSU Java API".

Installing XSU in the Database

Figure 11-3 shows the typical architecture for applications that use the XSU libraries installed in the database. 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. You deliver the XML internally through stored procedures in the database or externally through web or application servers.

In Figure 11-3 all lines are bidirectional. Because XSU can generate as well as save data, resources can deliver XML to XSU running inside the database, which can then insert it in the appropriate database tables.

Figure 11-3 Running XSU in the Database

Description of "Figure 11-3 Running XSU in the Database"

Installing XSU in an Application Server

Your application architecture may need to use an application server in the middle tier. The application tier can be an Oracle database, Oracle application server, or a third-party application server that supports Java programs.

You can 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 can install the XSU in your middle tier, thereby enabling your Java programs to make use of XSU through its Java API.

Figure 11-4 shows 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 process is bidirectional, which means that the data can be put back into the JDBC sources (database tables or views) by means of XSU. If an Oracle database itself is used as the application server, then you can use the PL/SQL front-end instead of Java.

Figure 11-4 Running XSU in the MIddle Tier

Description of "Figure 11-4 Running XSU in the MIddle Tier"

Installing XSU in a Web Server

Figure 11-5 shows that XSU can live in the Web server as long as the Web server supports Java servlets. In this way you can write Java servlets that use XSU. 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. To perform XML processing in the Web server and avoid intricate servlet programming, you can use the XSQL Servlet.

Figure 11-5 Running XSU in a Web Server

Description of "Figure 11-5 Running XSU in a Web Server"

Generating XML with the XSU Command-Line Utility

To generate XML from the database schema use the getXML parameter. For example, to generate an XML document by querying the employees table in the hr schema, you can use the following syntax:

java OracleXML getXML -user "hr/hr" "SELECT * FROM employees"

The preceding command performs the following tasks:

1. Connects to the current default database

2. Executes the specified SELECT query

3. Converts the SQL result set to XML

4. Prints the XML to standard output

The getXML parameter supports a wide range of options, which are explained in Table 11-2.

Generating XMLType Data with the XSU Command-Line Utility

You can use XSU to generate XML from tables with XMLType columns. Suppose that you run the demo script setup_xmltype.sql to create and populate the parts table. You can generate XML from this table with XSU as follows:

java OracleXML getXML -user "hr/hr" -rowTag "Part" "SELECT * FROM parts"

The output of the command is shown below:

<?xml version = '1.0'?>
<ROWSET>
   <Part num="1">
      <PARTNO>1735</PARTNO>
      <PARTNAME>Gizmo</PARTNAME>
      <PARTDESC>
         <Description>
           <Title>Description of the Gizmo</Title>
           <Author>John Smith</Author>
           <Body>
             The <b>Gizmo</b> is <i>grand</i>.
           </Body>
         </Description>
      </PARTDESC>
   </Part>
</ROWSET>

Performing DML with the XSU Command-Line Utility

To insert an XML document called new_employees.xml into the hr.employees table, use the following syntax:

java OracleXML putXML -user "hr/hr" -fileName "new_employees.xml" employees

The preceding command performs the following tasks:

1. Connects to the current database as hr

2. Reads the XML document named new_emp.xml

3. Parses the XML document, matching the tags with column names

4. Inserts the values appropriately into the employees table

The getXML parameter supports a wide range of options, which are explained in Table 11-2.

Running the testXMLSQL Program

To run the testXMLSQL.java program perform the following steps:

1. Compile testXMLSQL.java with javac.

2. Execute java testXMLSQL on the command line.

You must have the CLASSPATH pointing to this directory for the Java executable to find the class. Alternatively, use visual Java tools such as Oracle JDeveloper to compile and run this program. When run, this program prints out the XML file to the screen. The following shows sample output with some rows edited out:

<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMPLOYEE_ID>100</EMPLOYEE_ID>
      <FIRST_NAME>Steven</FIRST_NAME>
      <LAST_NAME>King</LAST_NAME>
      <EMAIL>SKING</EMAIL>
      <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
      <HIRE_DATE>6/17/1987 0:0:0</HIRE_DATE>
      <JOB_ID>AD_PRES</JOB_ID>
      <SALARY>24000</SALARY>
      <DEPARTMENT_ID>90</DEPARTMENT_ID>
   </ROW>
<!-- ROW num="2" through num="107" ... -->
</ROWSET>

Limiting the Number of Rows in the Result Set

In testXMLSQL.java and domTest.java, XSU generated XML from all rows returned by the query. Suppose that you query a table that contains 1000 rows, but you want only 100 rows at a time. One approach is to execute one query to obtain the first 100 rows, another to obtain the next 100 rows, and so on. With this technique you cannot skip the first five rows of the query and then generate the result. To avoid these problems, use the following Java methods:

• OracleXMLSave.setSkipRows() forces XSU to skip the desired number of rows before starting to generate the result. The command-line equivalent to this method is the -skipRows parameter.

• OracleXMLSave.setMaxRows() limits the number of rows converted to XML. The command-line equivalent to this method is the -maxRows parameter.

Example 11-1 sets skipRows to a value of 5 and maxRows to a value of 1, which causes XSU to skip the first 5 rows and then generate XML for the next row when querying the hr.employees table.

java OracleXML getXML -user "hr/hr" -skipRows 5 -maxRows 1 \
  "SELECT * FROM employees"

The following shows sample output (only row 6 of the query result set is returned):

<?xml version = '1.0'?>
<ROWSET>
   <ROW num="6">
      <EMPLOYEE_ID>105</EMPLOYEE_ID>
      <FIRST_NAME>David</FIRST_NAME>
      <LAST_NAME>Austin</LAST_NAME>
      <EMAIL>DAUSTIN</EMAIL>
      <PHONE_NUMBER>590.423.4569</PHONE_NUMBER>
      <HIRE_DATE>6/25/1997 0:0:0</HIRE_DATE>
      <JOB_ID>IT_PROG</JOB_ID>
      <SALARY>4800</SALARY>
      <MANAGER_ID>103</MANAGER_ID>
      <DEPARTMENT_ID>60</DEPARTMENT_ID>
   </ROW>
</ROWSET>

Keeping the Object Open for the Duration of the User's Session

In some situations you may want to keep the query object open for the duration of the user session. You can handle such cases with the maxRows() method and the keepObjectOpen() method.

Consider a Web search engine that paginates search results. The first page lists 10 results, the next page lists 10 more, and so on. To perform this task with XSU, request 10 rows at a time and keep the ResultSet open so that the next time you ask XSU for more results, it starts generating from where the last generation finished. If OracleXMLQuery creates a result set from the SQL query string, then it typically closes the ResultSet internally because it assumes no more results are required. Thus, you should invoke keepObjectOpen() to keep the cursor active.

A different case requiring an open query object is when the number of rows or number of columns in a row is very large. In this case, you can generate multiple small documents rather than one large document.

Paginating Results with OracleXMLQuery: Example

The paginateResults.java program shows how you can generate an XML page that paginates results. The output XML displays only 20 rows of the hr table.

The first step of the paginateResults.java program, which creates the connection, is the same as in testXMLSQL.java. The program continues as follows:

1. Create a SQL statement object and initialize it with a SQL query. The following code fragment sets two options in java.sql.ResultSet:

   Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
                                         ResultSet.CONCUR_READ_ONLY);
   

2. Create the query as a string and execute it by invoking Statement.executeQuery(). The return object is of type ResultSet. The following example illustrates this technique:

   String sCmd = "SELECT first_name, last_name FROM hr.employees"; 
   ResultSet rs = stmt.executeQuery(sCmd); 
   

3. Create the query object, as shown in the following code:

   OracleXMLQuery xmlQry = new OracleXMLQuery(conn, rs);
   

4. Configure the query object. The following code specifies that the query object should be open for the duration of the session. It also limits the number of rows returned to 20:

   xmlQry.keepObjectOpen(true); 
   xmlQry.setRowsetTag("ROWSET"); 
   xmlQry.setRowTag("ROW"); 
   xmlQry.setMaxRows(20); 
   

5. Retrieve the result as a String and print:

   String sXML = xmlQry.getXMLString(); 
   System.out.println(sXML);
   

After compiling the program, run it from the command line as follows:

java paginateResults

Inserting XML into All Columns with OracleXMLSave

The testInsert.java demo program inserts XML values into all columns of the hr.employees table.

The program follows these steps:

1. Create a JDBC OCI connection. The program calls the same getConnection() method used by the previous examples in this chapter:

   Connection conn = getConnection("hr","hr");
   

2. Create an XML save object. You initialize the object by passing it the Connection reference and the name of the table on which you want to perform DML. The following example illustrates this technique:

   OracleXMLSave sav = new OracleXMLSave(conn, "employees");
   

3. Insert the data in an input XML document into the hr.employees table. The following code fragment creates a URL from the document filename specified on the command line:

   sav.insertXML(sav.getURL(argv[0]));
   

4. Close the XML save object as follows:

   sav.close();
   

java testInsert "new_emp.xml"

INSERT INTO hr.employees 
  (employee_id, first_name, last_name, email, phone_number, hire_date, 
   salary, commission_pct, manager_id, department_id)
VALUES
  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);     

Inserting XML into a Subset of Columns with OracleXMLSave

In some circumstances you may not want to insert values into all columns. For example, the group of values that you obtain may not be the complete set, requiring you to use triggers or default values for the remaining columns. The testInsertSubset.java demo program shows how to handle this case.

The program follows these steps:

1. Create a JDBC OCI connection. The program calls the same getConnection() method used by the previous examples in this chapter:

   Connection conn = getConnection("hr","hr");
   

2. Create an XML save object. Initialize the object by passing it the Connection reference and the name of the table on which you want to perform DML. The following example illustrates this technique:

   OracleXMLSave sav = new OracleXMLSave(conn, "employees");
   

3. Create an array of strings. Each element of the array should contain the name of a column in which values will be inserted. The following code fragment specifies the names of five columns:

   String [] colNames = new String[5];
   colNames[0] = "EMPLOYEE_ID";
   colNames[1] = "LAST_NAME";
   colNames[2] = "EMAIL";
   colNames[3] = "JOB_ID";
   colNames[4] = "HIRE_DATE";
   

4. Configure the XML save object to update the specified columns. The following statement passes a reference to the array to the OracleXMLSave.setUpdateColumnList() method:

   sav.setUpdateColumnList(colNames);
   

5. Insert the data in an input XML document into the hr.employees table. The following code fragment creates a URL from the document filename specified on the command line:

   sav.insertXML(sav.getURL(argv[0]));
   

6. Close the XML save object as follows:

   sav.close();
   

java testInsert new_emp2.xml

INSERT INTO hr.employees (employee_id, last_name, email, job_id, hire_date) 
  VALUES (?, ?, ?, ?, ?);

Updating with Key Columns with OracleXMLSave

The testUpdate.java demo program updates the hr.employees table by invoking the OracleXMLSave.setKeyColumnList() method.

The testUpdate.java program follows these steps:

1. Create a JDBC OCI connection. The program calls the same getConnection() method used by the previous examples in this chapter:

   Connection conn = getConnection("hr","hr");
   

2. Create an XML save object. You initialize the object by passing it the Connection reference and the name of the table on which you want to perform DML. The following example illustrates this technique:

   OracleXMLSave sav = new OracleXMLSave(conn, "employees");
   

3. Create a single-element String array to hold the name of the primary key column in the table to be updated. The following code fragment specifies the name of the employee_id column:

   String [] keyColNames = new String[1];
   colNames[0] = "EMPLOYEE_ID";
   

4. Set the XML save object to the primary key specified in the array. The following statement passes a reference to the keyColNames array to the OracleXMLSave.setKeyColumnList() method:

   sav.setKeyColumnList(keyColNames);
   

5. Update the rows specified in the input XML document. The following statement creates a URL from the filename specified on the command line:

   sav.updateXML(sav.getURL(argv[0]));
   

6. Close the XML save object as follows:

   sav.close();
   

<?xml version='1.0'?>
<ROWSET>
 <ROW num="1">
    <EMPLOYEE_ID>7400</EMPLOYEE_ID>
    <SALARY>3250</SALARY>
 </ROW>
 <ROW num="2">
    <EMPLOYEE_ID>7369</EMPLOYEE_ID>
    <JOB_ID>SA_REP</JOB_ID>
    <MANAGER_ID>145</MANAGER_ID>
 </ROW>
<!-- additional rows ... -->
</ROWSET>

java testUpdate upd_emp.xml

UPDATE hr.employees SET salary = 3250 WHERE employee_id = 7400;

UPDATE hr.employees SET job_id = 'SA_REP' AND MANAGER_ID = 145 
  WHERE employee_id = 7369;

Updating a Column List with OracleXMLSave

You may want to update a table by using only a subset of the elements in an XML document. You can achieve this goal by specifying a list of columns. This technique speeds processing because XSU uses the same UPDATE statement with bind variables for all the ROW elements. You can also ignore other tags in the XML document.

Suppose you want to update the salary and job title for each employee and ignore the other data. If you know that all the elements to be updated are the same for all ROW elements in the XML document, then you can use the OracleXMLSave.setUpdateColumnNames() method to specify the columns. The testUpdateList.java program illustrates this technique.

The testUpdateList.java program follows these steps:

1. Create a JDBC OCI connection. The program calls the same getConnection() method used by the previous examples in this chapter:

   Connection conn = getConnection("hr","hr");
   

2. Create an XML save object. You initialize the object by passing it the Connection reference and the name of the table on which you want to perform DML. The following example illustrates this technique:

   OracleXMLSave sav = new OracleXMLSave(conn, "employees");
   

3. Create an array of type String to hold the name of the primary key column in the table to be updated. The array contains only one element, which is the name of the primary key column in the table to be updated. The following code fragment specifies the name of the employee_id column:

   String [] colNames = new String[1];
   colNames[0] = "EMPLOYEE_ID";
   

4. Set the XML save object to the primary key specified in the array. The following statement passes a reference to the colNames array to the OracleXMLSave.setKeyColumnList() method:

   sav.setKeyColumnList(keyColNames);
   

5. Create an array of type String to hold the name of the columns to be updated. The following code fragment specifies the name of the employee_id column:

   String[] updateColNames = new String[2];
   updateColNames[0] = "SALARY";
   updateColNames[1] = "JOB_ID";
   

6. Set the XML save object to the list of columns to be updated. The following statement performs this task:

   sav.setUpdateColumnList(updateColNames);
   

7. Update the rows specified in the input XML document. The following code fragment creates a URL from the filename specified on the command line:

   sav.updateXML(sav.getURL(argv[0]));
   

8. Close the XML save object as follows:

   sav.close();
   

java testUpdateList upd_emp2.xml

UPDATE hr.employees SET salary = 8350 AND job_id = 'AC_ACCOUNT' 
  WHERE employee_id = 100;

UPDATE hr.employees SET salary = 25000 AND job_id = 'AD_PRES' 
  WHERE employee_id = 206;

Deleting by Row with OracleXMLSave

The testDeleteRow.java demo program accepts an XML document filename as input and deletes the rows corresponding to the elements in the document.

The testDeleteRow.java program follows these steps:

1. Create a JDBC OCI connection. The program calls the same getConnection() method used by the previous examples in this chapter:

   Connection conn = getConnection("hr","hr");
   

2. Create an XML save object. You initialize the object by passing it the Connection reference and the name of the table on which you want to perform DML. The following example illustrates this technique:

   OracleXMLSave sav = new OracleXMLSave(conn, "employees");
   

3. Delete the rows specified in the input XML document. The following code fragment creates a URL from the filename specified on the command line:

   sav.deleteXML(sav.getURL(argv[0]));
   

4. Close the XML save object as follows:

   sav.close();
   

CONNECT hr/hr
ALTER TABLE job_history
  DISABLE CONSTRAINT JHIST_EMP_FK;
EXIT

java testDeleteRow upd_emp.xml

DELETE FROM hr.employees WHERE salary = 3250 AND employee_id = 7400;
DELETE FROM hr.employees WHERE job_id = 'SA_REP' AND MANAGER_ID = 145 
  AND employee_id = 7369;

Deleting by Key with OracleXMLSave

To only use the key values as predicates on the DELETE statement, invoke the OracleXMLSave.setKeyColumnList() method. This approach limits the number of elements used to identify a row, which has the benefit of improving performance by caching the DELETE statement and batching transactions. The testDeleteKey.java program illustrates this technique.

The testDeleteKey.java program follows these steps:

1. Create a JDBC OCI connection. The program calls the same getConnection() method used by the previous examples in this chapter:

   Connection conn = getConnection("hr","hr");
   

2. Create an XML save object. You initialize the object by passing it the Connection reference and the name of the table on which you want to perform DML. The following example illustrates this technique:

   OracleXMLSave sav = new OracleXMLSave(conn, "employees");
   

3. Create an array of type String to hold the name of the primary key column in the table. The array contains only one element. The following code fragment specifies the name of the employee_id column:

   String [] colNames = new String[1];
   colNames[0] = "EMPLOYEE_ID";
   

4. Set the XML save object to the primary key specified in the array. The following statement passes a reference to the colNames array to the OracleXMLSave.setKeyColumnList() method:

   sav.setKeyColumnList(keyColNames);
   

5. Delete the rows specified in the input XML document. The following code fragment creates a URL from the filename specified on the command line:

   sav.deleteXML(sav.getURL(argv[0]));
   

6. Close the XML save object as follows:

   sav.close();
   

java testInsert new_emp.xml
java testInsert new_emp2.xml

java testDeleteKey upd_emp.xml

DELETE FROM hr.employees WHERE employee_id=?;

DELETE FROM hr.employees WHERE employee_id = 7400;
DELETE FROM hr.employees WHERE employee_id = 7369;

Obtaining the Parent Exception

The testException.java demo program throws a runtime exception and then obtains the parent exception by invoking Exception.getParentException().

Running the preceding program generates the following error message:

Caught SQL Exception:ORA-00904: "SD": invalid identifier

Raising a No Rows Exception

When there are no rows to process, XSU returns a null string. You can throw an exception every time there are no more rows, however, so that the program can process this exception through exception handlers. When a program invokes OracleXMLQuery.setRaiseNoRowsException(), XSU raises an oracle.xml.sql.OracleXMLSQLNoRowsException whenever there are no rows to generate for the output. This is a runtime exception and need not be caught.

The noRowsTest.java demo program instantiates the pageTest class defined in pageTest.java. The condition to check the termination changed from checking whether the result is null to an exception handler.

The noRowsTest.java program creates a pageTest object and initializes it with a SQL query. The program proceeds as follows:

1. Configure the query object or raise a no rows exception. The following code fragment illustrates this technique:

   pageTest test = new pageTest("SELECT * from employees");
   ...
   test.qry.setRaiseNoRowsException(true);
   

2. Loop through the result set infinitely, retrieving ten rows at a time. When no rows are available, the program throws an exception. The following code fragment calls pageTest.nextPage(), which scrolls through the result set ten rows at a time:

   try
   {
     while(true)
       System.out.println(test.nextPage());
   }
   

3. Catch the no rows exception and print "END OF OUTPUT". The following code illustrates this technique:

   catch(oracle.xml.sql.OracleXMLSQLNoRowsException e)
   {
     System.out.println(" END OF OUTPUT "); 
     try
     {
       test.close();
     }
     catch ( Exception ae )
     {
       ae.printStackTrace(System.out);
     }
   }
   

After compiling the program, run it from the command line as follows:

java noRowsTest

Inserting Values into All Columns with DBMS_XMLSave

Run the insProc.sql demo script to create a PL/SQL stored procedure, insProc, which accepts the following parameters:

• An XML document as a CLOB

• The name of the table in which to insert the document

You can invoke the insProc procedure to insert an XML document into the table.

Run the insertClob.sql script to create a table called xmldocument and store an XML document in the table as a CLOB. The XML document describes employee 7370, Liz Gardner, whom you want to insert into the hr.employees table.

CREATE TABLE hr.xmldocument
  (docid   NUMBER PRIMARY KEY,
  xml_text CLOB);
-- insert an XML document into the CLOB column
INSERT INTO hr.xmldocument (docid,xml_text)
VALUES (1,
       '<?xml version="1.0"?>
       <ROWSET>
        <ROW num="1">
           <EMPLOYEE_ID>7370</EMPLOYEE_ID>
           <FIRST_NAME>Liz</FIRST_NAME>
           <LAST_NAME>Gardner</LAST_NAME>
           <EMAIL>liz.gardner@business.com</EMAIL>
           <PHONE_NUMBER>650-555-6127</PHONE_NUMBER>
           <HIRE_DATE>12/18/2004 0:0:0</HIRE_DATE>
           <SALARY>3000</SALARY>
           <COMMISSION_PCT>0</COMMISSION_PCT>
           <JOB_ID>SH_CLERK</JOB_ID>
           <MANAGER_ID>103</MANAGER_ID>
           <DEPARTMENT_ID>20</DEPARTMENT_ID>
        </ROW>
       </ROWSET>');

Run the insertEmployee.sql script shown in Example 11-4 to call the insProc stored procedure and insert Liz Gardner into the employees table.

DECLARE
  v_xml_text CLOB;
BEGIN
  SELECT xml_text 
    INTO v_xml_text
  FROM hr.xmldocument
  WHERE docid = 1;
  insProc(v_xml_text, 'employees');
END;
/

As in "Inserting Rows with OracleXMLSave", running the callinsProc procedure generates an INSERT statement of the form shown in Example 11-5.

INSERT INTO hr.employees 
  (employee_id, first_name, last_name, email, phone_number, hire_date, 
   salary, commission_pct, manager_id, department_id)
VALUES
  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);     

XSU matches the element tags in the input XML document that match the column names and binds their values.

Inserting into a Subset of Columns with DBMS_XMLSave

As explained in "Inserting XML into a Subset of Columns with OracleXMLSave", you may not want to insert values into all columns. You can create a list of column names for insert processing and pass it to the DBMS_XMLSave procedure. You can set these values by calling the setUpdateColumnName procedure repeatedly and passing in a column name to update every time. Clear the column name settings by invoking clearUpdateColumnList.

Run the testInsert.sql demo script to create a PL/SQL stored procedure called testInsert. You can use this procedure to insert XML data of type CLOB into the hr.employees table.

Run the insertClob2.sql script shown in Example 11-6 to insert an XML document describing new employee Jordan into a CLOB column of the xmldocument table. Note that the document does not contain an element corresponding to every column in the employees table.

-- insert an XML document into the CLOB column  of the xmldocument table with only 
-- some of the possible elements
INSERT INTO hr.xmldocument (docid, xml_text)
VALUES (2,
       '<?xml version="1.0"?>
       <ROWSET>
        <ROW num="1">
           <EMPLOYEE_ID>7401</EMPLOYEE_ID>
           <LAST_NAME>Jordan</LAST_NAME>
           <EMAIL>jim.jordan@business.com</EMAIL>
           <JOB_ID>SH_CLERK</JOB_ID>
           <HIRE_DATE>12/17/2004 0:0:0</HIRE_DATE>
        </ROW>
       </ROWSET>');

Running the insertEmployee2.sql script shown in Example 11-7 inserts the data for employee Jim Jordan into a subset of the columns in the hr.employees table.

DECLARE
  v_xml_text CLOB;
BEGIN
  SELECT xml_text 
    INTO v_xml_text
  FROM hr.xmldocument
  WHERE docid = 2;
  testInsert(v_xml_text);
END;
/

As in "Inserting XML into a Subset of Columns with OracleXMLSave", calling testInsert generates the following INSERT statement:

INSERT INTO hr.employees (employee_id, last_name, email, job_id, hire_date) 
  VALUES (?, ?, ?, ?, ?);

Updating with Key Columns with DBMS_XMLSave

Run the testUpdateKey.sql script to create a PL/SQL procedure called testUpdateKey. This procedure uses the employee_id column of the hr.employees table as a primary key.

Run the insertClob3.sql script shown in shown in Example 11-8 to insert an XML document into the CLOB column of the xmldocument table. The document specifies a new salary for employee 7400 and a new job ID and manager ID for employee 7369.

INSERT INTO hr.xmldocument (docid, xml_text)
VALUES (3,
       '<?xml version="1.0"?>
        <ROWSET>
         <ROW num="1">
            <EMPLOYEE_ID>7400</EMPLOYEE_ID>
            <SALARY>3250</SALARY>
         </ROW>
         <ROW num="2">
            <EMPLOYEE_ID>7369</EMPLOYEE_ID>
            <JOB_ID>SA_REP</JOB_ID>
            <MANAGER_ID>145</MANAGER_ID>
         </ROW>
        </ROWSET>');

Run the updateEmployee.sql script shown in Example 11-9 to pass the XML document to the testUpdateKey procedure and generate two UPDATE statements.

DECLARE
  v_xml_text CLOB;
BEGIN
  SELECT xml_text 
    INTO v_xml_text
  FROM hr.xmldocument
  WHERE docid = 3;
  testUpdateKey(v_xml_text);
END;
/

For the first ROW element, the program generates an UPDATE statement as follows:

UPDATE hr.employees SET salary = 3250 WHERE employee_id = 7400;

For the second ROW element the program generates the following statement:

UPDATE hr.employees SET job_id = 'SA_REP' AND MANAGER_ID = 145 
  WHERE employee_id = 7369;

Specifying a List of Columns with DBMS_XMLSave: Example

As described in "Updating a Column List with OracleXMLSave", you can specify a list of columns to update.

Run the testUpdateSubset.sql script creates the PL/SQL procedure testUpdateSubset. The procedure uses the employee_id column as key and updates only the salary and job_id columns of the hr.employees table.

Run the insertClob4.sql script to insert an XML document into the xmldocument table. The <ROW> elements in the document describe employees 100 and 206. Each <ROW> element has ten subelements that contain descriptive text.

Run the updateEmployee2.sql script shown in Example 11-10 to pass the XML CLOB to the testUpdateSubset procedure and generate two UPDATE statements.

DECLARE
  v_xml_text CLOB;
BEGIN
  SELECT xml_text 
    INTO v_xml_text
  FROM hr.xmldocument
  WHERE docid = 4;
  testUpdateSubset(v_xml_text);
END;
/

The procedure updates only those columns specified in the setUpdateColumn procedure, salary and email, for employees 100 and 206.

Deleting by Row with DBMS_XMLSave: Example

Create the testDeleteRow PL/SQL procedure by running the testDeleteRow.sql script. The procedure deletes a row from the hr.employees table for every <ROW> element in an input XML document.

Suppose that you want to delete the employee Jim Jordan that you added in Example 11-7. Run the deleteEmployeeByRow.sql script shown in Example 11-11 to pass the XML document as a CLOB to the testDeleteRow stored procedure.

DECLARE
  v_xml_text CLOB;
BEGIN
  SELECT xml_text 
    INTO v_xml_text
  FROM hr.xmldocument
  WHERE docid = 2;
  testDeleteRow(v_xml_text);
END;
/

The preceding call to testDeleteRow generates the following DELETE statement:

DELETE FROM hr.employees
  WHERE employee_id = 7401 AND last_name = 'JORDAN'
  AND email = 'jim.jordan@business.com' AND job_id = 'SH_CLERK'
  AND hire_date = '12/17/2004 0:0:0';

The program forms the DELETE statements based on the tag names present in each <ROW> element in the XML document.

Deleting by Key with DBMS_XMLSave: Example

As explained in "Deleting by Key with OracleXMLSave", you can specify a column to use as a primary key for the deletions. Use the DBMS_XMLSave.setKeyColumn function to specify the key.

The testDeleteKey procedure created by running testDeleteKey.sql deletes a row from the employees table for every <ROW> element in an input XML document.

Suppose that you want to delete the employee Liz Gardner that you added in Example 11-4. Run the deleteEmployeeByKey.sql script shown in Example 11-12 to pass the XML document as a CLOB to the testDeleteKey stored procedure.

DECLARE
  v_xml_text CLOB;
BEGIN
  SELECT xml_text 
    INTO v_xml_text
  FROM hr.xmldocument
  WHERE docid = 1;
  testDeleteKey(v_xml_text);
END;
/

In the preceding procedure call, XSU generates a single DELETE statement of the following form:

DELETE FROM hr.employees WHERE employee_id=?

XSU uses this statement for all ROW elements in the input XML document.

Default SQL to XML Mapping

Assume that you want to display data from some column of the hr.employees table as an XML document. You run XSU at the command line as follows:

java OracleXML getxml -user "hr/hr" -withschema \
  "SELECT employee_id, last_name, hire_date FROM employees"

XSU outputs an XML document based on the input query. The root element of the document is <DOCUMENT>. The following shows sample output, with extraneous lines replaced by comments:

<?xml version = '1.0'?>
<DOCUMENT xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <!-- children of schema element ... -->
   </xsd:schema>
   <ROWSET xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:noNamespaceSchemaLocation="#/DOCUMENT/xsd:schema[not(@targetNamespace)]">
      <ROW num="1">
         <EMPLOYEE_ID>100</EMPLOYEE_ID>
         <LAST_NAME>King</LAST_NAME>
         <HIRE_DATE>6/17/1987 0:0:0</HIRE_DATE>
      </ROW>
      <!-- additional rows ... -->
   </ROWSET>
</DOCUMENT>

In the generated XML, the rows returned by the SQL query are children of the <ROWSET> element. The XML document has the following features:

• The <ROWSET> element has zero or more <ROW> child elements corresponding to the number of rows returned. If the query generates no rows, then no <ROW> elements are included; if the query generates one row, then one <ROW> element is included, and so forth.

• Each <ROW> element contains data from one of the table rows. Specifically, each <ROW> element has one or more child elements whose names and content are identical to the database columns specified in the SELECT statement.

% java OracleXML getxml -user "hr/hr" -withschema "SELECT * FROM dept1"

<?xml version='1.0'?>
<DOCUMENT xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <schema targetNamespace="http://xmlns.oracle.com/xdb/SYSTEM"      
           xmlns="http://www.w3.org/2001/XMLSchema"
           xmlns:SYSTEM="http://xmlns.oracle.com/xdb/SYSTEM">
   <!-- children of schema element ... -->
   </xsd:schema>
   <ROWSET xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:noNamespaceSchemaLocation="#/DOCUMENT/xsd:schema[not(@targetNamespace)]">
      <ROW num="1">
         <DEPTNO>120</DEPTNO>
         <DEPTNAME>Treasury</DEPTNAME>
         <DEPTADDR>
            <STREET>2004 Charade Rd</STREET>
            <CITY>Seattle</CITY>
            <STATE>WA</STATE>
            <ZIP>98199</ZIP>
         </DEPTADDR>
         <EMPLIST>
            <EMPLIST_ITEM>
               <EMPLOYEE_ID>1</EMPLOYEE_ID>
               <LAST_NAME>Mehta</LAST_NAME>
               <SALARY>6000</SALARY>
               <EMPLOYEE_ADDRESS>
                  <STREET>500 Main Road</STREET>
                  <CITY>Seattle</CITY>
                  <STATE>WA</STATE>
                  <ZIP>98199</ZIP>
               </EMPLOYEE_ADDRESS>
            </EMPLIST_ITEM>
         </EMPLIST>
      </ROW>
   </ROWSET>
</DOCUMENT>
 

Default XML to SQL Mapping

XML to SQL mapping is the reverse of SQL to XML mapping. Consider the following differences when using XSU to map XML to SQL:

• When transforming XML to SQL, XSU ignores XML attributes. Thus, there is really no mapping of XML attributes to SQL.

• When transforming SQL to XML, XSU performs the mapping on a single ResultSet created by a SQL query. The query can span multiple database tables or views. When transforming XML into SQL, note the following:

  • 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, then you can use INSTEAD OF INSERT triggers.

If the XML document does not perfectly map to the target database schema, then you can perform the following actions:

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

• Modify the XML document by using XSLT to transform the XML document. You can register the XSLT stylesheet with XSU so that the incoming XML is automatically transformed before it attempts any mapping.

• Modify XSU's XML-to-SQL mapping. You can instruct XSU to perform case-insensitive matching of XML elements to database columns or attributes. For example, you can instruct XSU to do the following:

  • Use the name of the element corresponding to a database row instead of ROW.

  • Specify the date format to use when parsing dates in the XML document.

Customizing Generated XML

In some circumstances you may need to generate XML with a specific structure. Because the desired structure may differ from the default structure of the generated XML document, you want to have some flexibility in this process. You can customize the structure of a generated XML document by using one of the following methods:

• Altering the Database Schema or SQL Query

• Modifying XSU

<?xml version = "1.0"?>
<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>

java OracleXML putXML -user "hr/hr" -fileName customer.xml customer_tab

CREATE TABLE hr.cust_tab
 ( customerid NUMBER(10), 
   firstname VARCHAR2(20), 
   lastname VARCHAR2(20),
   street VARCHAR2(40),
   city VARCHAR2(20),
   state VARCHAR2(20),
   zip VARCHAR2(20)
 );

CREATE VIEW customer_view 
AS
SELECT customer_type(customerid, firstname, lastname,
       address_type(street,city,state,zip)) customer
FROM cust_tab;

java OracleXML putXML -user "hr/hr" -fileName customer.xml customer_view

java OracleXML getXML -user "hr/hr" -fileName "mapColumnToAtt.sql"

How XSU Queries the Database

XSU executes SQL queries and retrieves the ResultSet from the database. XSU then acquires and analyzes metadata about the ResultSet. Using the mapping described in "Default SQL to XML Mapping", XSU processes the SQL result set and converts it into an XML document.

XSU cannot handle certain types of queries, especially those that mix columns of type LONG or LONG RAW with CURSOR() expressions in the SELECT clause. LONG and LONG RAW are two examples of datatypes that JDBC accesses as streams and whose use is deprecated. If you migrate these columns to CLOBs, then the queries succeed.

How XSU Inserts Rows

When inserting the contents of an XML document into a table or view, XSU performs the following steps:

1. Retrieves metadata about the target table or view.

2. Generates a SQL INSERT statement based on the metadata. For example, assume that the target table is dept1 and the XML document is generated from dept1. XSU generates the following INSERT statement:

   INSERT INTO dept1 (deptno, deptname, deptaddr, emplist) VALUES (?,?,?,?)
   

3. Parses the XML document, and for each record, it binds the appropriate values to the appropriate columns or attributes. For example, it binds the values for INSERT statement as follows:

   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'),...)
   

4. Executes the statement. You can optimize INSERT processing to insert in batches and commit in batches.

How XSU Updates Rows

Updates and delete statements 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 if no triggers or constraints are on the table. With updates and deletes, the XML element can match more than one row if the matching columns are not key columns in the table.

For update statements, you must provide a list of key columns that XSU needs to identify the row to update. For example, assume that you have an XML document that contains the following fragment:

<ROWSET>
  <ROW num="1">
    <DEPTNO>100</DEPTNO>
    <DEPTNAME>SportsDept</DEPTNAME>
  </ROW>
</ROWSET>

You want to change the DEPTNAME value from Sports to SportsDept. If you supply the DEPTNO as the key column, then XSU generates the following UPDATE statement:

UPDATE dept1 SET deptname = ? WHERE deptno = ?

XSU binds the values in the following way:

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.

How XSU Deletes Rows

For deletes, you can choose to provide a set of key columns so that XSU can identify the rows to be deleted. If you do not provide the set of key columns, then the DELETE statement tries to match all the columns in the document. Assume that you pass the following document to XSU:

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

XSU builds a DELETE statement for each ROW element:

DELETE FROM dept1 WHERE deptno = ? AND deptname = ? AND deptaddr = ?

The binding is as follows:

deptno   <- 100
deptname <- sports
deptaddr <- addresstype('100 redwood shores pkwy','redwood city','ca',
            '94065')

How XSU Commits After DML

By default XSU performs no explicit commits. If AUTOCOMMIT is on, which is the default for a JDBC connection, then after each batch of statement executions XSU executes a COMMIT. You can override this behavior by turning AUTOCOMMIT off and then using setCommitBatch to specify the number of statement executions before XSU should commit. If an error occurs, then XSU rolls back to either the state the target table was in before the call to XSU, or the state after the last commit made during the current call to XSU.