|Oracle® XML DB Developer's Guide
10g Release 1 (10.1)
Part Number B10790-01
This chapter introduces you to the PL/SQL package
DBMS_XMLSTORE. This package is used to insert, update, and delete data from XML documents in object-relational tables.
This chapter contains these topics:
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
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
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.
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
This last step may be repeated multiple times, with several XML documents.
Close the context with the
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.
Example 11-1 Inserting data with specified columns
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; /
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.
Example 11-2 Updating Data With Key Columns
Consider the following PL/SQL procedure:
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
JOB_ID fields as follows:
UPDATE scott.emp SET SAL = 1800 AND DEPTNO = 30 WHERE EMPNO = 7369;
and for the second
UPDATE scott.emp SET SAL = 2000 AND HIREDATE = 12/31/1992 WHERE EMPNO = 2290;
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
Example 11-3 Simple deleteXML Example
Consider the following PL/SQL example:
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;/
If you use the same XML document as in the preceding update example, you end up with the following two
DELETE FROM scott.emp WHERE EMPNO=7369; DELETE FROM scott.emp WHERE EMPNO=2200;
DELETE statements are formed based on the tag names present in each
ROW element in the XML document.