Delete Processing

In the case of delete, you can set the list of key columns. These columns will be put as part of the where clause of the delete. If the key column names are not supplied, then a new delete statement will be created for each ROW element of the XML document where the list of columns in the where clause of the delete will match those in the ROW element.

XSU Example 11: Deleting Operations Per ROW (Java)

Consider the delete example shown below,

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

      // Assume that the user passes in this document as the first argument!
      sav.deleteXML(argv[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;
   }
}

If we use the same XML document shown for the update example, we would end up with two delete statements,

DELETE FROM scott.emp WHERE empno=7369 and sal=1800 and deptno=30; 
DELETE FROM scott.emp WHERE empno=2200 and sal=2000 and hiredate=12/31/1992;

The delete statements were formed based on the tag names present in each ROW element in the XML document.

XSU Example 12: Deleting Specified Key Values (Java)

If we instead want the delete to only use the key values as predicates, we can use the setKeyColNames function to set this.

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

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

      // Assume that the user passes in this document as the first argument!
      sav.deleteXML(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;
   }
}

Here a single delete statement of the form,

DELETE FROM scott.emp WHERE EMPNO=?

will be generated and used for all ROW elements in the document.