General Information
Library Note
Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx .
Purpose
Used to insert, update, and delete relational data with mapping of XML tag names to relational column names.
AUTHID
CURRENT_USER
Data Types
-- context handles
SUBTYPE ctxHandle IS NUMBER;
SUBTYPE ctxType IS NUMBER;
SUBTYPE conversionType IS NUMBER;
Dependencies
Documented
Yes
Security Model
Owned by SYS with EXECUTE granted to PUBLIC and
DVSYS
Source
{ORACLE_HOME}/rdbms/admin/dbmsxml.sql
Subprograms
CLEARKEYCOLUMNLIST
Clears the current key column list if one exists
dbms_xmlstore.clearKeyColumnList(ctxHdl IN ctxType);
TBD
CLEARUPDATECOLUMNLIST
Clears the current update column list if one exists
dbms_xmlstore.clearUpdateColumnList(ctxHdl IN ctxType);
See Demo at page bottom
CLOSECONTEXT
Closes the context based on its handle
dbms_xmlstore.closeContext(ctxHdl IN ctxHandle);
See Demo at page bottom
DELETEXML
Deletes the specified records from the XML doc
Overload 1
dbms_xmlstore.deleteXML(
ctxHdl IN ctxType,
xDoc IN VARCHAR2)
RETURN NUMBER;
TBD
Overload 2
dbms_xmlstore.deleteXML(
ctxHdl IN ctxType,
xDoc IN CLOB)
RETURN NUMBER;
TBD
Overload 3
dbms_xmlstore.deleteXML(
ctxHdl IN ctxType,
xDoc IN XMLTYPE)
RETURN NUMBER;
TBD
INSERTXML
Inserts an XML document into a table
Overload 1
dbms_xmlstore.insertXML(
ctxHdl IN ctxType,
xDoc IN VARCHAR2)
RETURN NUMBER;
See Demo at page bottom
Overload 2
dbms_xmlstore.insertXML(
ctxHdl IN ctxType,
xDoc IN CLOB)
RETURN NUMBER;
See Demo at page bottom
Overload 3
dbms_xmlstore.insertXML(
ctxHdl IN ctxType,
xDoc IN XMLTYPE)
RETURN NUMBER;
TBD
NEWCONTEXT
Creates a save context, and returns the context's handle
dbms_xmlstore.newContext(targetTable IN VARCHAR2) RETURN ctxHandle;
See Demo at page bottom
SETKEYCOLUMN
Adds a column to the key column list
dbms_xmlstore.setKeyColumn(
ctxHdl IN ctxType,
colName IN VARCHAR2);
See Demo at page bottom
SETPRESERVEWHITESPACE
Sets Preserve White Space
dbms_xmlstore.setPreserveWhiteSpace(
ctxHdl IN ctxType,
flag IN BOOLEAN);
TBD
SETROWTAG
Names the tag used in the XML document that enclose the XML elements to be referenced
dbms_xmlstore.setRowTag(
ctx IN ctxHandle,
rowTagName IN VARCHAR2);
TBD
SETUPDATECOLUMN
Adds a column to the update column list
dbms_xmlstore.setUpdateColumn(
ctxHdl IN ctxType,
colName IN VARCHAR2);
See Demo at page bottom
UPDATEXML
Updates the table for the specified XML document
Overload 1
dbms_xmlstore.updateXML(
ctxHdl IN ctxType,
xDoc IN VARCHAR2)
RETURN NUMBER;
See Demo at page bottom
Overload 2
dbms_xmlstore.updateXML(
ctxHdl IN ctxType,
xDoc IN CLOB)
RETURN NUMBER;
See Demo at page bottom
Overload 3
dbms_xmlstore.updateXML(
ctxHdl IN ctxType,
xDoc IN XMLTYPE)
RETURN NUMBER;
TBD
Demo
This demo shows usage of multiple pieces of DBMS_XMLSTORE functionality
conn hr/hr@pdbdev
CREATE TABLE xmlstore_tab AS
SELECT *
FROM employees
WHERE 1=2;
DECLARE
insCtx DBMS_XMLSTORE.ctxType;
rows NUMBER;
xmlDoc CLOB := '<ROWSET>
<ROW num="1">
<EMPLOYEE_ID>998</EMPLOYEE_ID>
<SALARY>1000</SALARY>
<DEPARTMENT_ID>42</DEPARTMENT_ID>
<HIRE_DATE>07-JUL-2011</HIRE_DATE>
<LAST_NAME>Morgan</LAST_NAME>
<EMAIL>c-dmorgan</EMAIL>
<JOB_ID>DBA</JOB_ID>
</ROW>
<ROW>
<EMPLOYEE_ID>999</EMPLOYEE_ID>
<SALARY>2000</SALARY>
<DEPARTMENT_ID>24</DEPARTMENT_ID>
<HIRE_DATE>01-MAY-2012</HIRE_DATE>
<LAST_NAME>Manfredi</LAST_NAME>
<EMAIL>wmanfredi</EMAIL>
<JOB_ID>DEVELOPER</JOB_ID>
</ROW>
</ROWSET>';
BEGIN
insCtx := dbms_xmlstore.newContext ('hr.xmlstore_tab');
dbms_xmlstore.clearUpdateColumnList (insCtx); -- clear previous settings
-- set columns to update
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 XML doc
rows := dbms_xmlstore.insertXML (insCtx, xmlDoc);
dbms_output.put_line(rows || ' row count');
-- close the context
dbms_xmlstore.closeContext (insCtx);
END;
/
SELECT employee_id, first_name, last_name
FROM xmlstore_tab;
DECLARE
updCtx dbms_xmlstore.ctxType ;
rows NUMBER;
xmlDoc CLOB := '<ROWSET>
<ROW>
<EMPLOYEE_ID>998</EMPLOYEE_ID>
<FIRST_NAME>Dan</FIRST_NAME>
</ROW>
</ROWSET>';
BEGIN
updCtx := dbms_xmlstore.newContext ('HR.XMLSTORE_TAB');
dbms_xmlstore.clearUpdateColumnList (updCtx);
-- identify employee_id is the "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, last_name
FROM xmlstore_tab;