Oracle DBMS_XMLSTORE
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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
DBMS_MACADM DBMS_XMLGEN_LIB XMLTYPE
Documented Yes
Security Model Owned by SYS with EXECUTE granted to PUBLIC
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
 
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

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;

Related Topics
DBMS_XMLGEN
DBMS_XMLPARSER
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved