Oracle 12c DBMS_XMLSTORE Demos

Oracle DBMS_XMLSTORE Version 12.1.0.2

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	INSERTXML	SETROWTAG
CLEARUPDATECOLUMNLIST	NEWCONTEXT	SETUPDATECOLUMN
CLOSECONTEXT	SETKEYCOLUMN	UPDATEXML
DELETEXML	SETPRESERVEWHITESPACE	Demo

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 (new 12.1)

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

vdbms_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;