Oracle | DBMS_XMLSTORE

Oracle DBMS_XMLSTORE Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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 and DVSYS

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

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;