XSU Insert Processing in PL/SQL

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 16: Inserting Values into All Columns (PL/SQL)

This example creates a procedure, insProc, which takes in an XML document as a CLOB and a table name to put the document into and then inserts the document:

create or replace procedure insProc(xmlDoc IN CLOB, tableName IN VARCHAR2) is
   insCtx DBMS_XMLSave.ctxType;
   rows number;
 begin
    insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle
    rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document
    DBMS_XMLSave.closeContext(insCtx);            -- this closes the handle
end;
/

This procedure can now be called with any XML document and a table name. For example, a call of the form,

insProc(xmlDocument, 'scott.emp');

will generate an insert statement of the form,

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

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 17: Inserting Values into Only Certain Columns (PL/SQL)

In certain cases, we may not want to insert values into all columns. This might 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. We create a list of column names that we want the insert to work on and then pass it to the DBMS_XMLSave procedure. The setting of these values can be done by calling the setUpdateColumnName() procedure repeatedly, passing in a column name to update every time. The column name settings can be cleared using the clearUpdateColumnNames().

create or replace procedure testInsert( xmlDoc IN clob) is
  insCtx DBMS_XMLSave.ctxType;
  doc clob;
  rows number;
begin
 
   insCtx := DBMS_XMLSave.newContext('scott.emp'); -- get the save context..!

   DBMS_XMLSave.clearUpdateColumnList(insCtx); -- clear the update settings

   -- set the columns to be updated as a list of values..
   DBMS_XMLSave.setUpdateColumn(insCtx,'EMPNO'); 
   DBMS_XMLSave.setUpdateColumn(insCtx,'ENAME');
   DBMS_XMLSave.setUpdatecolumn(insCtx,'JOB');

   -- Now insert the doc. This will only insert into EMPNO,ENAME and JOB columns
   rows := DBMS_XMLSave.insertXML(insCtx, xmlDoc); 
   DBMS_XMLSave.closeContext(insCtx);

end;
/

If we call the procedure passing in a CLOB as a document, 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.