Oracle DBMS_XMLSTORE
Version 19.2.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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 18cR3
What's New In 19cR2

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