13 Using the XML SQL Utility

Generating XML with the XSU Java API: Basic Process

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

The basic steps in Figure 13-1 are:

Figure 13-1 Generating XML with XSU

Description of "Figure 13-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, invoke 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 these options:

   • Create the connection with the JDBC Oracle Call Interface (OCI) driver. This code fragment shows 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","password");
     

     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. This code fragment shows this technique:

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

     The thin driver requires the host name (dlsun489), port number (1521), and the Oracle system identifier (SID), ORCL. The database must have an active Transmission Control Protocol/Internet Protocol (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 have to register the driver. Create the Connection object:

     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 this example:

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

3. Configure the XML query object by invoking OracleXMLQuery methods. This example specifies that only 20 rows are to be included in the result set:

   xmlQry.setMaxRows(20); 
   

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

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

   Get a string object:

   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 13-2 shows the basic process.

Figure 13-2 Storing XML in the Database Using XSU

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

The basic steps in Figure 13-2 are:

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 this example:

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

3. Specify the primary key columns. For example, this 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. This 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. This example shows 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 these 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 this 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 invoking a constructor-like function to get the handle, and then use the handle in all subsequent invocations.

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

Generating XML results in a character large object (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. Get a context handle by invoking the DBMS_XMLQuery.newContext function and supplying it the query, either as a CLOB or a VARCHAR2. This 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. This example specifies changes the default ROWSET element name to EMPSET:

   DBMS_XMLQuery.setRowSetTag(v_queryCtx,'EMPSET');
   

5. Fetch the results. You can get the XML as a CLOB with the getXML function, which generates XML with or without a DTD or XML schema. This 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 these variables:

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

   You can print the CLOB stored in v_result:

   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 invoking a constructor-like function to get the handle, and then use the handle in all subsequent invocations.

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 invoking 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 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. This 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 are used in the WHERE clause of the UPDATE statement and the tags matching the update column list are 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 13-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 servers or application servers.

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

Figure 13-3 Running XSU in the Database

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

Installing XSU in an Application Server

Your application architecture may require an application server in the middle tier. The application tier can be a database or an 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 13-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) with XSU. If a database is used as the application server, then you can use the PL/SQL front end instead of Java.

Figure 13-4 Running XSU in the Middle Tier

Description of "Figure 13-4 Running XSU in the Middle Tier"

Installing XSU in a Web Server

Figure 13-5 shows that XSU can live in the web server because 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 13-5 Running XSU in a Web Server

Description of "Figure 13-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 this syntax:

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

The preceding command performs these 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 13-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:

java OracleXML getXML -user "hr/password" -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 this syntax:

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

The preceding command performs these 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 13-2.

Running the testXMLSQL Program

To run the testXMLSQL.java program perform these 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. This code 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 get the first 100 rows, another to get 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 these 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 13-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/password" -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 might 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 must 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 shows 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 this code:

   OracleXMLQuery xmlQry = new OracleXMLQuery(conn, rs);
   

4. Configure the query object. The following code specifies that the query object is 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:

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 invokes the same getConnection() method used by the previous examples in this chapter:

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

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 shows 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 file name specified on the command line:

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

4. Close the XML save object:

   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 situations, you might not want to insert values into all columns. For example, the group of values that you get might 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 invokes the same getConnection() method used by the previous examples in this chapter:

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

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 shows this technique:

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

3. Create an array of strings. Each element of the array must contain the name of a column in which values are 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 file name specified on the command line:

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

6. Close the XML save object:

   sav.close();
   

java testInsert new_emp2.xml

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

Updating 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 invokes the same getConnection() method used by the previous examples in this chapter:

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

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 shows 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 file name specified on the command line:

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

6. Close the XML save object:

   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 shows this technique.

The testUpdateList.java program follows these steps:

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

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

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 shows 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 file name specified on the command line:

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

8. Close the XML save object:

   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 file name 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 invokes the same getConnection() method used by the previous examples in this chapter:

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

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 shows 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 file name specified on the command line:

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

4. Close the XML save object:

   sav.close();
   

CONNECT 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 use only 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 shows this technique.

The testDeleteKey.java program follows these steps:

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

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

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 shows 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 file name specified on the command line:

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

6. Close the XML save object:

   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;

Getting the Parent Exception

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

Running the preceding program generates this 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 shows 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 invokes 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 shows 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:

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 these 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 13-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 13-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 invoking 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 13-6 to insert an XML document describing new employee Jordan into a CLOB column of the xmldocument table. 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 13-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," invoking testInsert generates this INSERT statement:

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

Updating 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 13-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 13-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:

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

For the second ROW element the program generates this 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 13-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 you want to delete the employee Jim Jordan that you added in Example 13-7. Run the deleteEmployeeByRow.sql script shown in Example 13-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 invocation of testDeleteRow generates this 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 you want to delete the employee Liz Gardner that you added in Example 13-4. Run the deleteEmployeeByKey.sql script shown in Example 13-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 this 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

To display data from some column of the hr.employees table as an XML document, run XSU at the command line:

java OracleXML getXML -user "hr/password" -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 these 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 table row. 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/password" -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 these 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:

  • 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 these 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 style sheet 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 this:

  • 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 situations, you might have 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 these 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/password" -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/password" -fileName customer.xml customer_view

java OracleXML getXML -user "hr/password" -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 data types 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 these 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 this 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:

   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 must identify the row to update. For example, assume that you have an XML document that contains this 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 this UPDATE statement:

UPDATE dept1 SET deptname = ? WHERE deptno = ?

XSU binds the values in this 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 this 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:

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