Oracle 12c DBMS

Oracle DBMS_XMLSTORE Version 12.1.0.2

General Information

Library Note

Morgan's Library Page Header

The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. 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.

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.

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

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;