Oracle DBMS_XMLSTORE
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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_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
 
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;

Related Topics
Built-in Functions
Built-in Packages
DBMS_XMLGEN
DBMS_XMLPARSER
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved