Insert Processing

The steps to insert a document into a table or view is to simply supply the table or the view name and then the document. The utility parses the document (if a string is given) and then creates an insert statement which it binds all the values into. By default, the utility inserts values into all the columns of the table or view and an absent element is treated as a NULL value. The following code shows how the document generated from the emp table can be put back into it with relative ease.

XSU Example 7: Inserting XML Values into all Columns (Java)

This example inserts XML values into all columns:

import java.sql.*;
import oracle.xml.sql.dml.OracleXMLSave;
public class testInsert
{
   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. Save it in to the table.!
      sav.insertXML(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;
   }
}

An insert statement of the form,

insert into scott.emp (EMPNO, ENAME, JOB, MGR, SAL, DEPTNO) VALUES(?,?,?,?,?,?);

will be generated and the element tags in the input XML document matching the column names will be matched and their values bound. For the code snippet shown above, if we send it the XML document,

 <?xml version='1.0'?>
<ROWSET>
	 	 <ROW num="1">
 	 	  <EMPNO>7369</EMPNO>
    <ENAME>Smith</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>12/17/1980 0:0:0</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </ROW>
  <!-- additional rows ... -->
</ROWSET>

we would have a new row in the emp table containing the values (7369, Smith, CLERK, 7902, 12/17/1980,800,20). Any element absent inside the row element would have been taken as a null value.

XSU Example 8: Inserting XML Values into Only Certain Columns (Java)

In certain cases, you may not want to insert values into all columns. This may be true when the values that we are getting is not the complete set and we need triggers or default values to be used for the rest of the columns. The code below shows how this can be done.

Assume that we are getting the values only for the employee number, name and job and the salary, manager, deptno and hiredate field gets filled in automatically. First create a list of column names that we want the insert to work on and then pass it to the OracleXMLSave instance.

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

      String [] colNames = new String[5];
      colNames[1] = "EMPNO";
      colNames[2] = "ENAME";
      colNames[3] = "JOB";

      sav.setUpdateColumnList(colNames); // set the columns to update..!

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

An insert statement of the form,

insert into scott.emp (EMPNO, ENAME, JOB) VALUES (?, ?, ?); 

is generated. Note that in the above example, if the inserted document contains values for the other columns (JOB, HIREDATE etc.), those will be ignored.

Also an insert is performed for each ROW element that is present in the input. These inserts are batched by default.