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:
The DBMS_XMLSTORE
package enables DML operations to be performed on relational tables using XML. It takes a canonical XML mapping similar to that produced by package 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, so it 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 CLOB
and VARCHAR.
PL/SQL functions insertXML
, updateXML
, and deleteXML
, which are also present in package DBMS_XMLSAVE
, have been enhanced in package DBMS_XMLSTORE
to take XMLType
instances in addition to CLOB
values and strings. This provides for better integration with Oracle XML DB functionality.
To use PL/SQL package DBMS_XMLSTORE
, follow these steps:
Create a context handle by calling function DBMS_XMLSTORE.
newContext
and supplying it with the table name to use for the DML operations. For case sensitivity, double-quote the string that 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 package 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 calling function DBMS_XMLSTORE.
setUpdateColumn
for each column. This is highly recommended, since it will improve performance. The default behavior is to insert values for all of the columns whose corresponding elements are present in the XML document.
For Updates: You must specify one or more (pseudo-) key columns using function DBMS_XMLSTORE.
setKeyColumn
. These are used to specify which rows are to be updated. In SQL, you would do that using a WHERE
clause in an UPDATE
statement, specifying a combination of columns that uniquely identify the rows to be updated. The columns that you use with setKeyColumn
need not be actual keys of the table — as long as they uniquely specify a row, they can be used.
For example, in the employees
table, column employee_id
uniquely identifies rows (it is in fact a key of the table). If the XML document that you use to update the table contains element <EMPLOYEE_ID>2176</EMPLOYEE_ID>
, then the rows where employee_id
equals 2176
are updated.
The list of update columns can also be specified, using DBMS_XMLSTORE.setUpdateColumn
. This is recommended, for better performance. The default behavior is to update all of the columns in the row(s) identified by setKeyColumn
whose corresponding elements are present in the XML document.
For Deletions: As for updates, you specify (pseudo-) key columns to identify the row(s) to delete.
Provide a document to PL/SQL function insertXML
, updateXML
, or deleteXML
.
This last step may be repeated multiple times, with several XML documents.
Close the context with function DBMS_XMLSTORE.
closeContext
.
To insert an XML document into a table or view, you supply the table or view name and 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.
Example 12-1 Inserting data with specified columns
This example uses DBM_XMLSTORE
to insert the information for two new employees into the employees
table. The information is provided in the form of XML data.
SELECT employee_id AS EMP_ID, salary, hire_date, job_id, email, last_name FROM employees WHERE department_id = 30; EMP_ID SALARY HIRE_DATE JOB_ID EMAIL LAST_NAME ------ ---------- --------- ---------- ---------- ---------- 114 11000 07-DEC-94 PU_MAN DRAPHEAL Raphaely 115 3100 18-MAY-95 PU_CLERK AKHOO Khoo 116 2900 24-DEC-97 PU_CLERK SBAIDA Baida 117 2800 24-JUL-97 PU_CLERK STOBIAS Tobias 118 2600 15-NOV-98 PU_CLERK GHIMURO Himuro 119 2500 10-AUG-99 PU_CLERK KCOLMENA Colmenares 6 rows selected. DECLARE insCtx DBMS_XMLSTORE.ctxType; rows NUMBER; xmlDoc CLOB := '<ROWSET> <ROW num="1"> <EMPLOYEE_ID>920</EMPLOYEE_ID> <SALARY>1800</SALARY> <DEPARTMENT_ID>30</DEPARTMENT_ID> <HIRE_DATE>17-DEC-2002</HIRE_DATE> <LAST_NAME>Strauss</LAST_NAME> <EMAIL>JSTRAUSS</EMAIL> <JOB_ID>ST_CLERK</JOB_ID> </ROW> <ROW> <EMPLOYEE_ID>921</EMPLOYEE_ID> <SALARY>2000</SALARY> <DEPARTMENT_ID>30</DEPARTMENT_ID> <HIRE_DATE>31-DEC-2004</HIRE_DATE> <LAST_NAME>Jones</LAST_NAME> <EMAIL>EJONES</EMAIL> <JOB_ID>ST_CLERK</JOB_ID> </ROW> </ROWSET>'; BEGIN insCtx := DBMS_XMLSTORE.newContext('HR.EMPLOYEES'); -- 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, 'EMPLOYEE_ID'); DBMS_XMLSTORE.setUpdateColumn(insCtx, 'SALARY'); DBMS_XMLSTORE.setUpdateColumn(insCtx, 'HIRE_DATE'); DBMS_XMLSTORE.setUpdateColumn(insCtx, 'DEPARTMENT_ID'); DBMS_XMLSTORE.setUpdateColumn(insCtx, 'JOB_ID'); DBMS_XMLSTORE.setUpdateColumn(insCtx, 'EMAIL'); DBMS_XMLSTORE.setUpdateColumn(insCtx, 'LAST_NAME'); -- Insert the doc. rows := DBMS_XMLSTORE.insertXML(insCtx, xmlDoc); DBMS_OUTPUT.put_line(rows || ' rows inserted.'); -- Close the context DBMS_XMLSTORE.closeContext(insCtx); END; / 2 rows inserted. PL/SQL procedure successfully completed. SELECT employee_id AS EMP_ID, salary, hire_date, job_id, email, last_name FROM employees WHERE department_id = 30; EMP_ID SALARY HIRE_DATE JOB_ID EMAIL LAST_NAME ------ ---------- --------- ---------- ---------- ---------- 114 11000 07-DEC-94 PU_MAN DRAPHEAL Raphaely 115 3100 18-MAY-95 PU_CLERK AKHOO Khoo 116 2900 24-DEC-97 PU_CLERK SBAIDA Baida 117 2800 24-JUL-97 PU_CLERK STOBIAS Tobias 118 2600 15-NOV-98 PU_CLERK GHIMURO Himuro 119 2500 10-AUG-99 PU_CLERK KCOLMENA Colmenares 920 1800 17-DEC-02 ST_CLERK STRAUSS Strauss 921 2000 31-DEC-04 ST_CLERK EJONES Jones 8 rows selected.
To update (modify) existing data using package DBMS_XMLSTORE
, you must specify which rows to update. In SQL, you would do that using a WHERE
clause in an UPDATE
statement. With DBMS_XMLSTORE
, you do it by calling procedure setKeyColumn
once for each of the columns that are used collectively to identify the row.
You can think of this set of columns as acting like a set of key columns: together, they specify a unique row to be updated. However, the columns that you use (with setKeyColumn
) need not be actual keys of the table — as long as they uniquely specify a row, they can be used with calls to setKeyColumn
.
Example 12-2 Updating Data With Key Columns
This example uses DBM_XMLSTORE
to update information. Assuming that the first name for employee number 188 is incorrectly recorded as Kelly, this example corrects that first name to Pat. Since column employee_id
is in fact a primary key for table employees
, a single call to setKeyColumn
specifying column employee_id
is sufficient to identify a unique row for updating.
SELECT employee_id, first_name FROM employees WHERE employee_id = 188; EMPLOYEE_ID FIRST_NAME ----------- ---------- 188 Kelly 1 row selected. DECLARE updCtx DBMS_XMLSTORE.ctxType; rows NUMBER; xmlDoc CLOB := '<ROWSET> <ROW> <EMPLOYEE_ID>188</EMPLOYEE_ID> <FIRST_NAME>Pat</FIRST_NAME> </ROW> </ROWSET>'; BEGIN updCtx := DBMS_XMLSTORE.newContext('HR.EMPLOYEES'); -- get the context DBMS_XMLSTORE.clearUpdateColumnList(updCtx); -- clear update settings -- Specify that column employee_id is a "key" to identify the row to update. DBMS_XMLSTORE.setKeyColumn(updCtx, 'EMPLOYEE_ID'); rows := DBMS_XMLSTORE.updateXML(updCtx, xmlDoc); -- update the table DBMS_XMLSTORE.closeContext(updCtx); -- close the context END; / SELECT employee_id, first_name FROM employees WHERE employee_id = 188; EMPLOYEE_ID FIRST_NAME ----------- ---------- 188 Pat 1 row selected.
This UPDATE
statement is equivalent to the use of DBM_XMLSTORE
in this example:
UPDATE hr.employees SET first_name = 'Pat' WHERE employee_id = 188;
Deletions are treated similarly to updates: you specify the (pseudo-) key columns that identify the rows to delete.
Example 12-3 Simple deleteXML() Example
SELECT employee_id FROM employees WHERE employee_id = 188;
EMPLOYEE_ID
-----------
188
1 row selected.
DECLARE
delCtx DBMS_XMLSTORE.ctxType;
rows NUMBER;
xmlDoc CLOB :=
'<ROWSET>
<ROW>
<EMPLOYEE_ID>188</EMPLOYEE_ID>
<DEPARTMENT_ID>50</DEPARTMENT_ID>
</ROW>
</ROWSET>';
BEGIN
delCtx := DBMS_XMLSTORE.newContext('HR.EMPLOYEES');
DBMS_XMLSTORE.setKeyColumn(delCtx, 'EMPLOYEE_ID');
rows := DBMS_XMLSTORE.deleteXML(delCtx, xmlDoc);
DBMS_XMLSTORE.closeContext(delCtx);
END;
/
SELECT employee_id FROM employees WHERE employee_id = 188;
no rows selected.