Oracle DBMS_XMLSAVE
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Save XML to relational tables. The package body source code is in clear, unwrapped, text.

This package is deprecated in 18.1 in favor of DBMS_XMLSTORE.
AUTHID CURRENT_USER
Constants
Name Data Type Value
DEFAULT_DATE_FORMAT VARCHAR2(21) 'MM/dd/yyyy HH:mm:ss';
DEFAULT_ROWTAG VARCHAR2(3) 'ROW'
IGNORE_CASE NUMBER 1
MATCH_CASE NUMBER 0
Data Types SUBTYPE ctxType IS NUMBER;
Dependencies Only Standard
Documented No
First Available Not known
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/xdk/admin/dbmsxsu.sql
Subprograms
 
CLEARKEYCOLUMNLIST
Clears the key column list dbms_xmlsave.clearKeyColumnList(ctxHdl IN ctxType);
CREATE TABLE orders (
order_id          INTEGER,
order_revision    INTEGER,
order_date        DATE          NOT NULL,
order_header_hash INTEGER       NOT NULL,
order_currency    VARCHAR2(3)   NOT NULL,
order_language    VARCHAR2(3)   NOT NULL,
machine_name      VARCHAR2(20)  NOT NULL,
assembly_name     VARCHAR2(255) NOT NULL,
user_name         VARCHAR2(20)  NOT NULL,
calling_meth_name VARCHAR2(255) NOT NULL);

ALTER TABLE orders
ADD CONSTRAINT pk_orders
PRIMARY KEY (order_id, order_revision);

CREATE INDEX ix_orders_order_date
ON orders(order_date);

CREATE INDEX ix_orders_header_hash
ON orders(order_header_hash);

DECLARE
 xmlCtx dbms_xmlsave.ctxType;
BEGIN
  xmlCtx := dbms_xmlsave.newContext('ORDERS');
  dbms_xmlsave.clearKeyColumnList(xmlCtx);
  dbms_xmlsave.clearUpdateColumnList(xmlCtx);
  dbms_xmlsave.closeContext(xmlCtx);
END;
/
 
CLEARUPDATECOLUMNLIST
Clears the update column list dbms_xmlsave.clearUpdateColumnList(ctxHdl IN ctxType);
See ClearKeyColumnList Demo Above
 
CLOSECONTEXT
Closes/deallocates a particular save context dbms_xmlsave.closeContext(ctxHdl IN ctxType);
See ClearKeyColumnList Demo Above
 
DELETEXML
Deletes records specified by data from the XML document, from specified table
Overload 1
dbms_xmlsave.deleteXML(
ctxHdl IN ctxType,
xDoc   IN VARCHAR2)
RETURN NUMBER;
TBD
Overload 2 dbms_xmlsave.deleteXML(
ctxHdl IN ctxType,
xDoc   IN CLOB)
RETURN NUMBER;
TBD
 
GETEXCEPTIONCONTENT
Returns the thrown exception's error code and error message dbms_xmlsave.deleteXML(
ctxHdl IN  ctxType,
errNo  OUT NUMBER,
errMsg OUT VARCHAR2);
TBD
 
INSERTXML
Inserts the XML document into the table specified at the context creation time
Overload 1
dbms_xmlsave.insertXML(
ctxHdl IN ctxType,
xDoc   IN VARCHAR2)
RETURN NUMBER;
conn scott/tiger@pdbdev

CREATE TABLE emp2 AS
SELECT * FROM scott.emp
WHERE 1=2;

DECLARE
 l_clob clob := '<?xml version = "1.0"?>
  <ROWSET>
    <ROW num="1">
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7902</MGR>
      <HIREDATE>12/17/1980 0:0:0</HIREDATE>
      <SAL>800</SAL>
      <DEPTNO>20</DEPTNO>
    </ROW>
    <ROW num="2">
      <EMPNO>7499</EMPNO>
      <ENAME>ALLEN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/20/1981 0:0:0</HIREDATE>
      <SAL>1600</SAL>
      <COMM>300</COMM>
      <DEPTNO>30</DEPTNO>
    </ROW>
    <ROW num="3">
      <EMPNO>7521</EMPNO>
      <ENAME>WARD</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/22/1981 0:0:0</HIREDATE>
      <SAL>1250</SAL>
      <COMM>500</COMM>
      <DEPTNO>30</DEPTNO>
    </ROW>
    <ROW num="4">
      <EMPNO>7566</EMPNO>
      <ENAME>JONES</ENAME>
      <JOB>MANAGER</JOB>
      <MGR>7839</MGR>
      <HIREDATE>4/2/1981 0:0:0</HIREDATE>
      <SAL>2975</SAL>
      <DEPTNO>20</DEPTNO>
    </ROW>
    <ROW num="5">
      <EMPNO>7654</EMPNO>
      <ENAME>MARTIN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>9/28/1981 0:0:0</HIREDATE>
      <SAL>1250</SAL>
      <COMM>1400</COMM>
      <DEPTNO>30</DEPTNO>
    </ROW>
    <ROW num="6">
      <EMPNO>7698</EMPNO>
      <ENAME>BLAKE</ENAME>
      <JOB>MANAGER</JOB>
      <MGR>7839</MGR>
      <HIREDATE>5/1/1981 0:0:0</HIREDATE>
      <SAL>2850</SAL>
      <DEPTNO>30</DEPTNO>
    </ROW>
    <ROW num="7">
      <EMPNO>7782</EMPNO>
      <ENAME>CLARK</ENAME>
      <JOB>MANAGER</JOB>
      <MGR>7839</MGR>
      <HIREDATE>6/9/1981 0:0:0</HIREDATE>
      <SAL>2450</SAL>
      <DEPTNO>10</DEPTNO>
    </ROW>
    <ROW num="8">
      <EMPNO>7788</EMPNO>
      <ENAME>SCOTT</ENAME>
      <JOB>ANALYST</JOB>
      <MGR>7566</MGR>
      <HIREDATE>4/19/1987 0:0:0</HIREDATE>
      <SAL>3000</SAL>
      <DEPTNO>20</DEPTNO>
    </ROW>
    <ROW num="9">
      <EMPNO>7839</EMPNO>
      <ENAME>KING</ENAME>
      <JOB>PRESIDENT</JOB>
      <HIREDATE>11/17/1981 0:0:0</HIREDATE>
      <SAL>5000</SAL>
      <DEPTNO>10</DEPTNO>
    </ROW>
    <ROW num="10">
      <EMPNO>7844</EMPNO>
      <ENAME>TURNER</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>9/8/1981 0:0:0</HIREDATE>
      <SAL>1500</SAL>
      <COMM>0</COMM>
      <DEPTNO>30</DEPTNO>
    </ROW>
    <ROW num="11">
      <EMPNO>7876</EMPNO>
      <ENAME>ADAMS</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7788</MGR>
      <HIREDATE>5/23/1987 0:0:0</HIREDATE>
      <SAL>1100</SAL>
      <DEPTNO>20</DEPTNO>
    </ROW>
    <ROW num="12">
      <EMPNO>7900</EMPNO>
      <ENAME>JAMES</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7698</MGR>
      <HIREDATE>12/3/1981 0:0:0</HIREDATE>
      <SAL>950</SAL>
      <DEPTNO>30</DEPTNO>
    </ROW>
    <ROW num="13">
      <EMPNO>7902</EMPNO>
      <ENAME>FORD</ENAME>
      <JOB>ANALYST</JOB>
      <MGR>7566</MGR>
      <HIREDATE>12/3/1981 0:0:0</HIREDATE>
      <SAL>3000</SAL>
      <DEPTNO>20</DEPTNO>
    </ROW>
    <ROW num="14">
      <EMPNO>7934</EMPNO>
      <ENAME>MILLER</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7782</MGR>
      <HIREDATE>1/23/1982 0:0:0</HIREDATE>
      <SAL>1300</SAL>
      <DEPTNO>10</DEPTNO>
    </ROW>
  </ROWSET>';

 l_ctx dbms_xmlsave.ctxType;
 l_rows number;
BEGIN
  l_ctx := dbms_xmlsave.newContext('EMP2');
  l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
  dbms_xmlsave.closeContext(l_ctx);
  dbms_output.put_line(l_rows || ' rows inserted...');
END insert_xml_emps;
/
Overload 2 dbms_xmlsave.insertXML(
ctxHdl IN ctxType,
xDoc   IN CLOB)
RETURN NUMBER;
TBD
 
NEWCONTEXT
Creates a save context, and returns the context handle dbms_xmlsave.newContext(targetTable IN VARCHAR2) RETURN ctxType;
See ClearKeyColumnList Demo Above
 
PROPAGATEORIGINALEXCEPTION
Tells the XSU that if an exception is raised, the XSU should throw the exception rather then, wrapping it with an OracleXMLSQLException dbms_xmlsave.propagateOriginalException(
ctxHdl IN ctxType,
flag   IN BOOLEAN);
TBD
 
P_PROPAGATEORIGINALEXCEPTION
Undocumented workaround related to an ORA-00600 [kgmexchi11] dbms_xmlsave.p_propagateOriginalException(
ctxHdl IN ctxType,
flag   IN NUMBER);
TBD
 
P_SETPRESERVEWHITESPACE
Undocumented workaround related to an ORA-00600 [kgmexchi11] dbms_xmlsave.p_setPreserveWhiteSpace(
ctxHdl IN ctxType,
flag   IN NUMBER);
TBD
 
P_SETSQLTOXMLNAMEESC
Undocumented workaround related to an ORA-00600 [kgmexchi11] dbms_xmlsave.p_setSQLToXMLNameEsc(
ctxHdl IN ctxType,
flag   IN NUMBER);
TBD
 
P_SETXSLT
Undocumented workaround related to an ORA-00600 [kgmexchi11]
Overload 1
dbms_xmlsave.p_setXSLT(
ctxHdl IN ctxType,
uri    IN VARCHAR2,
ref    IN VARCHAR2);
TBD
Overload 2 dbms_xmlsave.p_setXSLT(
ctxHdl     IN ctxType,
stylesheet IN CLOB,
ref        IN VARCHAR2);
TBD
 
P_USEDBDATES
Undocumented workaround related to an ORA-00600 [kgmexchi11] dbms_xmlsave.p_useDBDates(
ctxHdl IN ctxType,
flag   IN NUMBER);
TBD
 
REMOVEXSLTPARAM
Removes the value of a top-level stylesheet parameter dbms_xmlsave.removeXSLTParam(
ctxHdl IN ctxType,
name   IN VARCHAR2);
TBD
 
SETBATCHSIZE
Changes the batch size used during DML operations dbms_xmlsave.setBatchSize(
ctxHdl    IN ctxType,
batchSize IN NUMBER);
DECLARE
 l_ctx dbms_xmlsave.ctxType;
BEGIN
  l_ctx := dbms_xmlsave.newContext('EMP2');
  dbms_xmlsave.setBatchSize(l_ctx, 250);
END;
/
 
SETCOMMITBATCH
Sets the commit batch size dbms_xmlsave.setCommitBatch(
ctxHdl    IN ctxType,
batchSize IN NUMBER);
DECLARE
 l_ctx dbms_xmlsave.ctxType;
BEGIN
  l_ctx := dbms_xmlsave.newContext('EMP2');
  dbms_xmlsave.setCommitBatch(l_ctx, 100);
END;
/
 
SETDATEFORMAT
Sets the format of the generated dates in the XML document dbms_xmlsave.setDateFormat(
ctxHdl IN ctxType,
mask   IN VARCHAR2);
DECLARE
 l_ctx dbms_xmlsave.ctxType;
BEGIN
  l_ctx := dbms_xmlsave.newContext('EMP2');
  dbms_xmlsave.setDateFormat(l_ctx, dbms_xmlsave.default_date_format);
END;
/
 
SETIGNORECASE
The XSU does mapping of XML elements to database dbms_xmlsave.setIgnoreCase(
ctxHdl IN ctxType,
flag   IN NUMBER);
DECLARE
 cnt    INTEGER;
 xmlCtx dbms_xmlsave.ctxType;
 xmlTxt CLOB :='<Log><Item><OldValue>jones</OldValue><NewValue>JONES</NewValue></Item>'
||'<Item><OldValue>smith</OldValue><NewValue>SMITH</NewValue></Item></Log>';
BEGIN
  xmlCtx := dbms_xmlsave.newContext('T');
  dbms_xmlsave.setIgnoreCase(xmlCtx, 1);
  dbms_xmlsave.closeContext(xmlCtx);
END;
/
 
SETKEYCOLUMN
Adds a column to the key column list dbms_xmlsave.setKeyColumn(ctxHdl IN ctxType, colName IN VARCHAR2);
TBD
 
SETPRESERVEWHITESPACE
Tells the XSU whether to preserve whitespace or not dbms_xmlsave.setPreserveWhitespace(
ctxHdl IN ctxType,
flag   IN BOOLEAN := TRUE);
DECLARE
 l_ctx dbms_xmlsave.ctxType;
BEGIN
  l_ctx := dbms_xmlsave.newContext('EMP2');
  dbms_xmlsave.setPreserveWhitespace(l_ctx, TRUE);
END;
/
 
SETROWTAG
Names the tag used in the XML document to enclose the XML elements corresponding to database dbms_xmlsave.setRowTag(ctxHdl IN ctxType, tag IN VARCHAR2);
TBD
 
SETSQLTOXMLNAMEESCAPING
This turns on or off escaping of XML tags in the case that the SQL object name, which is mapped to a XML identifier, is not a valid XML identifier dbms_xmlsave.setSQLToXMLNameEscaping(
ctxHdl IN ctxType,
flag   IN BOOLEAN := TRUE);
DECLARE
 l_ctx dbms_xmlsave.ctxType;
BEGIN
  l_ctx := dbms_xmlsave.newContext('EMP2');
  dbms_xmlsave.setSQLToXMLNameEscaping(l_ctx, TRUE);
END;
/
 
SETUPDATECOLUMN
Adds a column to the update column list dbms_xmlsave.setUpdateColumn(
ctxHdl  IN ctxType,
colName IN VARCHAR2);
TBD
 
SETXSLT
Registers a XSL transform to be applied to the XML to be saved
Overload 1
dbms_xmlsave.setXSLT(
ctxHdl IN ctxType,
uri    IN VARCHAR2,
ref    IN VARCHAR2 := NULL);
TBD
Overload 2 dbms_xmlsave.setXSLT(
ctxHdl     IN ctxType,
stylesheet IN CLOB,
ref        IN VARCHAR2 := NULL);
TBD
 
SETXSLTPARAM
Sets the value of a top-level stylesheet parameter dbms_xmlsave.setXSLTParam(
ctxHdl IN ctxType,
name   IN VARCHAR2,
value  IN VARCHAR2);
TBD
 
UPDATEXML
Updates the table given the XML document
Overload 1
dbms_xmlsave.updateXML(
ctxHdl IN ctxType,
xDoc   IN VARCHAR2) RETURN NUMBER;
TBD
Overload 2 dbms_xmlsave.updateXML(
ctxHdl IN ctxType,
xDoc   IN CLOB)
RETURN NUMBER;
TBD
 
USEDBDATES
Used Database Dates dbms_xmlsave.useDBDates(
ctxHdl IN ctxType,
flag   IN BOOLEAN := TRUE);
DECLARE
 xmlCtx dbms_xmlsave.ctxType;
BEGIN
  xmlCtx := dbms_xmlsave.newContext('ORDERS');
  dbms_xmlsave.useDBDates(xmlCtx, TRUE);
  dbms_xmlsave.closeContext(xmlCtx);
END;
/

Related Topics
Built-in Functions
Built-in Packages
DBMS_XMLGEN
DBMS_XMLSTORE
What's New In 12cR1
What's New In 12cR2
XML Functions

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