Oracle DBMS_XMLSTORE Demos

Oracle DBMS_XMLSTORE Version 11.2.0.3

General Information

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

Security Model

Owned by SYS with EXECUTE granted to PUBLIC

Source

{ORACLE_HOME}/rdbms/admin/dbmsxml.sql

Subprograms

CLEARKEYCOLUMNLIST	INSERTXML	SETUPDATECOLUMN
CLEARUPDATECOLUMNLIST	NEWCONTEXT	UPDATEXML
CLOSECONTEXT	SETKEYCOLUMN	Demo
DELETEXML	SETROWTAG

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;