11 DBMS_XMLSTORE

Overview of DBMS_XMLSTORE

The DBMS_XMLSTORE package enables DML operations to be performed on relational tables using XML. It takes a canonical XML mapping, similar to the one produced by DBMS_XMLGEN, converts it to object relational constructs, and inserts, updates or deletes the value from relational tables.

The functionality of the DBMS_XMLSTORE package is similar to that of the DBMS_XMLSAVE package which is part of the Oracle XML SQL Utility. There are, however, several key differences: DBMS_XMLSTORE is written in C and compiled into the kernel and hence provides higher performance.

• DBMS_XMLSTORE uses SAX to parse the input XML document and hence has higher scalability and lower memory requirements. DBMS_XMLSTORE allows input of XMLType in addition to CLOBs and VARCHAR.

• While DBMS_XMLSAVE is a wrapper around a Java class, DBMS_XMLSTORE is implemented in C inside the database. This should significantly improve performance.

• DBMS_XMLSTORE uses SAX parsing of the incoming XML documents, which provides much greater scalability than the DOM parsing used in DBMS_XMLSAVE.

• The insertXML(), updateXML(), and deleteXML() functions, which are also present in DBMS_XMLSAVE, have been enhanced in DBMS_XMLSTORE to take XMLTypes in addition to CLOBs and strings. This provides for better integration with Oracle XML DB functionality.

Using DBMS_XMLSTORE

To use DBMS_XMLSTORE follow these steps:

• Create a context handle by calling the DBMS_XMLSTORE.newContext() function and supplying it with the table name to use for the DML operations. For case sensitivity, double-quote the string which is passed to the function.

  By default, XML documents are expected to identify rows with the <ROW> tag. This is the same default used by DBMS_XMLGEN when generating XML. This may be overridden by calling the setRowTag function.

• For Inserts: You can set the list of columns to insert using the setUpdateColumn function for each column. This is highly recommended since it will improve performance. The default is to insert values for all the columns whose corresponding elements are present in the XML document.

• For Updates: You must specify one or more key columns using the setKeyColumn function. The key columns are used to specify which rows are to be updated, like the where clause in a SQL update statement. For example, if you set EMPLOYEE_ID as a key column, and the XML document contains "<EMPLOYEE_ID>2176</EMPLOYEE_ID>", then rows where EMPLOYEE_ID equals 2176 are updated. The list of update columns can also be specified and is recommended for performance. The default is to update all the columns whose corresponding elements are present in the XML document.

• For Deletes: Key columns may be set to specify which columns are used for the where clause. The default is for all columns present to be used. Specifying the columns is recommended for performance.

• Provide a document to one of insertXML, updateXML, or deleteXML.

• This last step may be repeated multiple times, with several XML documents.

• Close the context with the closeContext function.

Insert Processing with DBMS_XMLSTORE

To insert an XML document into a table or view, simply supply the table or the view name and then the document. DBMS_XMLSTORE parses the document and then creates an INSERT statement into which it binds all the values. By default, DBMS_XMLSTORE inserts values into all the columns represented by elements in the XML document. The following example shows you how the XML document generated from the Employees table, can be stored in the table with relative ease.

DECLARE
  insCtx DBMS_XMLStore.ctxType;
  rows NUMBER;
  xmldoc CLOB :=
    '<ROWSET>
       <ROW num="1">
         <EMPNO>7369</EMPNO>
         <SAL>1800</SAL>
         <HIREDATE>27-AUG-1996</HIREDATE>
       </ROW>
       <ROW>
         <EMPNO>2290</EMPNO>
         <SAL>2000</SAL>
         <HIREDATE>31-DEC-1992</HIREDATE>
       </ROW>
     </ROWSET>';
BEGIN
  insCtx := DBMS_XMLStore.newContext('scott.emp'); -- get saved context
  DBMS_XMLStore.clearUpdateColumnList(insCtx); -- clear the update settings 
  -- set the columns to be updated as a list of values 
  DBMS_XMLStore.setUpdateColumn(insCtx,'EMPNO'); 
  DBMS_XMLStore.setUpdateColumn(insCtx,'SAL'); 
  DBMS_XMLStore.setUpdatecolumn(insCtx,'HIREDATE'); 
  -- Now insert the doc.
  -- This will only insert into EMPNO, SAL and HIREDATE columns
  rows := DBMS_XMLStore.insertXML(insCtx, xmlDoc);
  -- Close the context
  DBMS_XMLStore.closeContext(insCtx); 
END;
/

Update Processing with DBMS_XMLSTORE

Now that you know how to insert values into the table from XML documents, let us see how to update only certain values. If you 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>
    <HIRE_DATE>31-DEC-1992</HIRE_DATE>
  <!-- additional rows ... -->
</ROWSET>

you can call the update processing to update the values. In the case of update, you need to supply the list of key column names. These form part of the WHERE clause in the UPDATE statement. In the employees table shown earlier, the employee number EMPLOYEE_ID column forms the key that you use for updates.

CREATE OR REPLACE PROCEDURE testUpdate (xmlDoc IN CLOB) IS
  updCtx DBMS_XMLStore.ctxType; 
  rows NUMBER;
BEGIN
   updCtx := DBMS_XMLStore.newContext('scott.emp');  -- get the context
   DBMS_XMLStore.clearUpdateColumnList(updCtx);      -- clear the update settings
   DBMS_XMLStore.setKeyColumn(updCtx,'EMPNO');       -- set EMPNO as key column
   rows := DBMS_XMLStore.updateXML(updCtx,xmlDoc);   -- update the table
   DBMS_XMLStore.closeContext(updCtx);               -- close the context
END;/

In this example, when the procedure is executed with a CLOB value that contains the document described earlier, two UPDATE statements are generated. For the first ROW element, you would generate an UPDATE statement to update the SALARY and JOB_ID fields as follows:

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;

Delete Processing with DBMS_XMLSTORE

For deletes, you can set the list of key columns. These columns are used in the WHERE clause of the DELETE statement. If the key column names are not supplied, then a new DELETE statement is created for each ROW element of the XML document where the list of columns in the WHERE clause of the DELETE matches those in the ROW element.

CREATE OR REPLACE PROCEDURE testDelete(xmlDoc IN CLOB) IS
  delCtx DBMS_XMLStore.ctxType;
  rows NUMBER;
BEGIN
  delCtx  := DBMS_XMLStore.newContext('scott.emp');
  DBMS_XMLStore.setKeyColumn(delCtx,'EMPNO');
  rows := DBMS_XMLStore.deleteXML(delCtx, xmlDoc);
  DBMS_XMLStore.closeContext(delCtx);
END;/

DELETE FROM scott.emp WHERE EMPNO=7369;
DELETE FROM scott.emp WHERE EMPNO=2200;