Update Processing

Now that we know how to insert values into the table from XML documents, let us see how to update only certain values. If we get an XML document to update the salary of an employee and also the department that she works in,

<ROWSET>
	 	 <ROW num="1">
 	 	  <EMPNO>7369</EMPNO>
    <SAL>1800</SAL>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>2290</EMPNO>
    <SAL>2000</SAL>
    <HIREDATE>12/31/1992</HIREDATE>
  <!-- additional rows ... -->
</ROWSET>

we can call the update processing to update the values. In the case of update, we need to supply the utility with the list of key column names. These form part of the where clause in the update statement. In the emp table shown above, the employee number (EMPNO) column forms the key and we use that for updates.

XSU Example 9: Updating Using the keyColumns (Java)

This example updates the emp table using keyColumns:

import java.sql.*;
import oracle.xml.sql.dml.OracleXMLSave;
public class testUpdate
{
   public static void main(String argv[])
     throws SQLException
   {
      Connection conn = getConnection("scott","tiger");
      OracleXMLSave sav = new OracleXMLSave(conn, "scott.emp");

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

      // Assume that the user passes in this document as the first argument!
      sav.updateXML(argv[1]);
      sav.close();
   }
   // Get the connection given the user name and password..!
    private static Connection getConnection(String user, String passwd)
      throws SQLException
    {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      Connection conn =
          DriverManager.getConnection("jdbc:oracle:oci8:@",user,passwd);
     return conn;
   }
}

In this example, two update statements would be generated. For the first ROW element, we would generate an update statement to update the SAL and JOB fields as shown below:-

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

and for the second ROW element,

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

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

However, in a lot of cases we might want to specify the list of columns to update. This would speed up the processing since the same update statement can be used for all the ROW elements. Also we can ignore other tags which occur in the document. Note that when we specify a list of columns to update, an element corresponding to one of the update columns, if absent, will be treated as NULL.

If we know that all the elements to be updated are the same for all the ROW elements in the XML document, then we can use the setUpdateColumnNames() function to set the list of columns to update.

import java.sql.*;
import oracle.xml.sql.dml.OracleXMLSave;
public class testUpdate
{
   public static void main(String argv[])
     throws SQLException
   {
      Connection conn = getConnection("scott","tiger");
      OracleXMLSave sav = new OracleXMLSave(conn, "scott.emp");

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

      // we create the list of columns to update..!
      // Note that if we do not supply this, then for each ROW element in the
      // XML document, we would generate a new update statement to update all
      // the tag values (other than the key columns)present in that element.
      String[] updateColNames = new String[2];
      updateColNames[1] = "SAL";
      updateColNames[2] = "JOB";
      sav.setUpdateColumnList(updateColNames); // set the columns to update..!

      // Assume that the user passes in this document as the first argument!
      sav.updateXML(argv[1]);
      sav.close();
   }
   // Get the connection given the user name and password..!
    private static Connection getConnection(String user, String passwd)
      throws SQLException
    {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      Connection conn =
          DriverManager.getConnection("jdbc:oracle:oci8:@",user,passwd);
     return conn;
   }
}