|Oracle8i Application Developer's Guide - XML
Release 3 (8.1.7)
Part Number A86030-01
Using XML-SQL Utility (XSU), 24 of 26
In the case of delete, you can set the list of key columns. These columns will be put as part of the where clause of the delete. If the key column names are not supplied, then a new delete statement will be created for each ROW element of the XML document where the list of columns in the where clause of the delete will match those in the ROW element.
Consider the delete example shown below,
create or replace procedure testDelete(xmlDoc IN clob) is delCtx DBMS_XMLSave.ctxType; rows number; begin delCtx := DBMS_XMLSave.newContext('scott.emp'); DBMS_XMLSave.setKeyColumn(delCtx,'EMPNO'); rows := DBMS_XMLSave.deleteXML(delCtx,xmlDoc); DBMS_XMLSave.closeContext(delCtx); end; /
If we use the same XML document shown for the update example, we would end up with two delete statements,
DELETE FROM scott.emp WHERE empno=7369 and sal=1800 and deptno=30; DELETE FROM scott.emp WHERE empno=2200 and sal=2000 and hiredate=12/31/1992;
The delete statements were formed based on the tag names present in each ROW element in the XML document.
If we instead want the delete to only use the key values as predicates, we can use the
setKeyColNames function to set this.
create or replace package testDML AS saveCtx DBMS_XMLSave.ctxType := null; -- a single static variable procedure insertXML(xmlDoc in clob); procedure updateXML(xmlDoc in clob); procedure deleteXML(xmlDoc in clob); end; / create or replace package body testDML AS rows number; procedure insertXML(xmlDoc in clob) is begin rows := DBMS_XMLSave.insertXML(saveCtx,xmlDoc); end; procedure updateXML(xmlDoc in clob) is begin rows := DBMS_XMLSave.updateXML(saveCtx,xmlDoc); end; procedure deleteXML(xmlDoc in clob) is begin rows := DBMS_XMLSave.deleteXML(saveCtx,xmlDoc); end; begin saveCtx := DBMS_XMLSave.newContext('scott.emp'); -- create the context once..! DBMS_XMLSave.setKeyColumn(saveCtx, 'EMPNO'); -- set the key column name. end; /
Here a single delete statement of the form,
will be generated and used for all ROW elements in the document.
In all the three cases described above, insert, update and delete, the same context handle can be used to do more than one operation. i.e. one can perform more than one insert using the same context provided all of those inserts are going to the same table that was specified when creating the save context. The context can also be used to mix updates, deletes and inserts.
For example, the following code shows how one can use the same context and settings to insert, delete or update values depending on the user's input.
The example uses a package static variable to store the context so that the same context can be used for all the function calls.
create or replace package testDML AS saveCtx DBMS_XMLSave.ctxHandle := null; -- a single static variable procedure insert(xmlDoc in clob); procedure update(xmlDoc in clob); procedure delete(xmlDoc in clob); end; / create or replace package body testDML AS procedure insert(xmlDoc in clob) is begin DBMS_XMLSave.insertXML(xmlDoc); end; procedure update(xmlDoc in clob) is begin DBMS_XMLSave.updateXML(xmlDoc); end; procedure delete(xmlDoc in clob) is begin DBMS_XMLSave.deleteXML(xmlDoc); end; begin saveCtx := DBMS_XMLSave.getCtx('scott.emp'); -- create the context once..! DBMS_XMLSave.setKeyColumnName('EMPNO'); -- set the key column name. end; end; /
In the above package, we create a context once for the whole package (thus the session) and then reuse the same context for performing inserts, udpates and deletes. Note that the key column ('EMPNO') would be used both for updates and deletes as a way of identifying the row.
The users of this package can now call any of the three routines to update the emp table.
All of these calls would use the same context. This would improve the performance of these operations, particularly if these operations are performed frequently.