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 18: Updating an XML Document Using keyColumns(PL/SQL)

,.......

create or replace procedure testUpdate ( xmlDoc IN clob) is
  updCtx DBMS_XMLSave.ctxType; 
  rows number;
begin
   
   updCtx := DBMS_XMLSave.newContext('scott.emp');  -- get the context
   DBMS_XMLSave.clearUpdateColumnList(updCtx); -- clear the update settings..
  
   DBMS_XMLSave.setKeyColumn(updCtx,'EMPNO'); -- set EMPNO as key column
   rows := DBMS_XMLSave.updateXML(updCtx,xmlDoc);  -- update the table.
   DBMS_XMLSave.closeContext(updCtx);             -- close the context..!

end;
/

In this example, when the procedure is executed with a CLOB value that contains the document described above, 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 19: Specifying a List of Columns to Update (PL/SQL)

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 setUpdateColumnName() procedure to set the column name to update.

create or replace procedure testUpdate(xmlDoc IN CLOB) is
  updCtx DBMS_XMLSave.ctxType;
  rows number;
begin
  
   updCtx := DBMS_XMLSave.newContext('scott.emp');
   DBMS_XMLSave.setKeyColumn(updCtx,'EMPNO'); -- set EMPNO as key column

   -- set list of columnst to update.
   DBMS_XMLSave.setUpdateColumn(updCtx,'SAL');
   DBMS_XMLSave.setUpdateColumn(updCtx,'JOB');

   rows := DBMS_XMLSave.updateXML(updCtx,xmlDoc); -- update the XML document..!
   DBMS_XMLSave.closeContext(updCtx);   -- close the handle

end;
/