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

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

The XSU PL/SQL API reflects the Java API in the generation and storage of XML documents from and to a database. Package DBMS_XMLQuery reflects the methods in Java class OracleXMLQuery.

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

DBMS_XMLSave is the PL/SQL package that reflects the methods in Java class OracleXMLSave. This package has a context handle associated with it. You create a context by invoking a constructor-like function to get the handle, and then use the handle in all subsequent invocations.

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

The typical architecture is shown for applications that use the XSU libraries installed in the database.

Figure 13-3 illustrates this typical architecture. 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"

You can run 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 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"

You can run XSU in a web server because the web server supports Java servlets.

Figure 13-5 shows XSU running in a web server.

Figure 13-5 Running XSU in a Web Server

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

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.

To generate XML from the database schema use the getXML parameter.

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

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

You can use XSU to generate XML from tables with XMLType columns.

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

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

An example shows how to insert an XML document into a database table.

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

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 testXMLSQL program is described.

1. Compile testXMLSQL.java with javac.
2. Execute java testXMLSQL on the command line.

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

Different ways to limit the number of rows in a result set are described.

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

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

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

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.

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

java paginateResults

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

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

Topics:

• Running the testInsert Program
  The textInsert program is described.

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.

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

Topics:

• Running the testInsertSubset Program
  The testInsertSubset program is described.

Demo program testUpdate.java invokes method OracleXMLSave.setKeyColumnList() to update table hr.employees.

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

Topics:

• Running the testUpdate Program
  The testUpdate program is described.

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

Topics:

• Running the testUpdateList Program
  The testUpdateList program is described.

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

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

Topics:

• Running the testDelete Program
  The testDelete program is described.

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.

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

Topics:

• Running the testDeleteKey Program
  The testDeleteKey program is described.

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

Running the program generates this error message:

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

When there are no rows to process, XSU returns a null string. You can throw an exception each time there are no more rows, however, so that a program can process this exception using exception handlers.

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

java noRowsTest

Run the insProc.sql demo script to create a PL/SQL stored procedure, insProc.

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

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

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

You can create a list of column names for insert processing and pass it to PL/SQL procedure DBMS_XMLSave.

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

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

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

Example scripts show you how to update key columns using a PL/SQL procedure.

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;

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

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

You can specify a list of columns to update and pass it to PL/SQL procedure DBMS_XMLSave.

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

You can delete a row from a table using a PL/SQL procedure.

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

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

You can specify a column to use as a primary key for the deletions. Use PL/SQL function DBMS_XMLSave.setKeyColumn to specify the key.

DELETE FROM hr.employees WHERE employee_id=?

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