Oracle DBMS_XMLPARSER
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 Parses (accesses) the contents of data stored in XML documents. Default parser behavior changes must be made prior to calling procedure in this package.
AUTHID CURRENT_USER
Constants
Name Data Type Value
INTERNAL_ERR NUMBER -20000
PARSE_ERR NUMBER -20100
FILE_ERR NUMBER -20101
CONN_ERR NUMBER -20102
NULL_ERR NUMBER -20103
Data Types TYPE parser IS RECORD (id dbms_xmldom.domtype);
Dependencies
DBMS_CLOBUTIL DBMS_XMLSTORAGE_MANAGE WWV_MIG_FRM_LOAD_XML
DBMS_METADATA_INT UTL_FILE WWV_MIG_FRM_OLB_LOAD_XML
DBMS_XMLDOM WWV_FLOW_XLIFF WWV_MIG_RPT_LOAD_XML
DBMS_XMLPARSER_LIB WWV_MIG_FRMMENU_LOAD_XML XMLPARSER
Documented Yes
Exceptions See Constants Above
Security Model Owned by XDB with EXECUTE granted to PUBLIC and APEX_040200
Source {ORACLE_HOME}/rdbms/admin/dbmsxmlp.sql
Subprograms
 
FREEPARSER
Frees a parser object dbms_xmlparser.freeParser(p IN dbms_xmlparser.parser);
See code demos at page bottom
 
GETBASEDIR
Returns the base directory used to resolve relative URLs dbms_xmlparser.getBaseDir(p IN dbms_xmlparser.parser) RETURN VARCHAR2;
See code demos at page bottom
 
GETDOCTYPE
Gets DTD parsed. It must be called only after a DTD is parsed dbms_xmlparser.getDocType(p IN dbms_xmlparser.parser) RETURN dbms_xmldom.DOMDocumentType;
TBD
 
GETDOCUMENT
Gets a DOM Document built by the parser - MUST be called only after a document is parsed dbms_xmlparser.getDocument(p IN dbms_xmlparser.parser) RETURN dbms_xmldom.DOMDocument;
See code demos at page bottom
 
GETERRORLOG
Returns the error log file if one exists dbms_xmlparser.getErrorLog(p IN dbms_xmlparser.parser) RETURN VARCHAR2;
See code demos at page bottom
 
GETRELEASEVERSION
Returns the current parser version for PL/SQL dbms_xmlparser.getReleaseVersion RETURN VARCHAR2;
SELECT dbms_xmlparser.getReleaseVersion
FROM dual;
 
GETVALIDATIONMODE
Returns validation mode dbms_xmlparser.getValidationMode(p IN dbms_xmlparser.parser) RETURN BOOLEAN;
See code demos at page bottom
 
NEWPARSER
Creates a new parser instance and returns its handle dbms_xmlparser.parser RETURN dbms_xmlparser.parser;
See code demos at page bottom
 
PARSE
Parses XML stored in a URL or file
Overload 1
dbms_xmlparser.parse(url IN VARCHAR2, csid IN NUMBER := 0) RETURN dbms_xmldom.DOMDocument;
TBD
Overload 2 dbms_xmlparser.parse(p IN Parser, url IN VARCHAR2, csid IN NUMBER := 0);
TBD
 
PARSEBUFFER
Parses XML stored in a buffer dbms_xmlparser.parseBuffer(p IN dbms_xmlparser.parser, doc IN VARCHAR2);
TBD
 
PARSECLOB
Parses XML stored in a clob dbms_xmlparser.parseCLOB(p IN dbms_xmlparser.parser, doc IN CLOB);
See code demos at page bottom
 
PARSEDTD
Parses DTD stored in a URL or file dbms_xmlparser.parseDTD(
p    IN dbms_xmlparser.parser,
url  IN VARCHAR2,
root IN VARCHAR2,
csid IN NUMBER :=0);
TBD
 
PARSEDTDBUFFER
Parses DTD stored in a buffer dbms_xmlparser.parseDTDBuffer(p IN dbms_xmlparser.parser, dtd IN VARCHAR2, root IN VARCHAR2);
TBD
 
PARSEDTDCLOB
Parses DTD stored in a CLOB dbms_xmlparser.parseDTDClob(p IN dbms_xmlparser.parser, dtd IN CLOB, root IN VARCHAR2);
TBD
 
RETAINCDATASECTION
This is a no-op procedure added strictly for compatibility with XDK dbms_xmlparser.retainCDataSection(p IN dbms_xmlparser.parser, flag IN BOOLEAN);
In violation to the W3C spec, XDK allows a CDATA section to be parsed. If the appl does not want this behavior then a value of FALSE is passed to this procedure. Since XDB will never parse CDATA sections, calling this procedure has no effect.

See code demos at page bottom
 
SETBASEDIR
Sets base directory used to resolve relative URLs dbms_xmlparser.setBaseDir(p IN dbms_xmlparser.parser, dir IN VARCHAR2);
See code demos at page bottom
 
SETDOCTYPE
Sets DTD for validation purposes that must be done before an xml document is parsed dbms_xmlparser.setDocType(p IN dbms_xmlparser.parser dtd IN dbms_xmldom.DOMDocumentType);
TBD
 
SETERRORLOG
Sets errors to be sent to the specified file dbms_xmlparser.setErrorLog(p IN dbms_xmlparser.parser, fileName IN VARCHAR2);
TBD
 
SETPRESERVEWHITESPACE
Sets white space preserve mode dbms_xmlparser.setPreserveWhitespace(p IN dbms_xmlparser.parser, yes IN BOOLEAN);
See code demos at page bottom
 
SETVALIDATIONMODE
Sets the parser validation mode dbms_xmlparser.setValidationMode(p IN dbms_xmlparser.parser, yes IN BOOLEAN);
See code demos at page bottom
 
SHOWWARNINGS
Turns parser warnings on or off dbms_xmlparser.showWarnings(p IN dbms_xmlparser.parser, yes IN BOOLEAN);
See code demos at page bottom
 
WRITEERRORS
Internal function that writes errors to the errorlog file dbms_xmlparser.writeErrors(
p       IN dbms_xmlparser.parser,
err_num IN NUMBER,
err_msg IN VARCHAR2);
TBD
 
Demos
This code demonstrates multiple functions and procedures in this package

Before running this demo download the emp.xml file and store it at the location pointed to by the CTEMP directory object.
conn sys@pdborcl as sysdba

SELECT *
FROM dba_directories
WHERE directory_name = 'XMLDIR';

GRANT read ON DIRECTORY xmldir TO uwclass;

conn scott/tiger@pdborcl

GRANT select ON emp TO uwclass;

conn uwclass/uwclass

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

set serveroutput on

DECLARE
 pVar      dbms_xmlparser.parser;
 ErrLogDir VARCHAR2(100);
BEGIN
  pVar := dbms_xmlparser.newParser;

  dbms_xmlparser.setBaseDir(pVar, 'CTEMP');

  dbms_xmlparser.showWarnings(pVar, TRUE);

  dbms_xmlparser.setPreserveWhitespace(pVar, TRUE);

  dbms_xmlparser.setValidationMode(pVar, TRUE);

  IF dbms_xmlparser.getValidationMode(pVar) THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;

  ErrLogDir := dbms_xmlparser.getErrorLog(pVar);
  dbms_output.put_line('Error Log: ' || ErrLogDir);

  dbms_xmlparser.retainCDataSection(pVar, FALSE);

  dbms_output.put_line('BaseDir: ' || dbms_xmlparser.getBaseDir(pVar));

  dbms_xmlparser.freeParser(pVar);
END;
/
This code demonstrates an integration between DBMS_XMLPARSER with the DBMS_LOB and DBMS_XMLDOM packages set serveroutput on

DECLARE
 bfVar   BFILE;
 clobVar CLOB;
 pVar    dbms_xmlparser.parser;
 DOMDoc  dbms_xmldom.DOMDocument;
 DomNL   dbms_xmldom.DOMNodeList;
 DomN    dbms_xmldom.DOMNode;

 TYPE emp_row_tab IS TABLE OF emp%ROWTYPE;
 emptab_t emp_row_tab := emp_row_tab();
BEGIN
  bfVar := bFileName('CTEMP', 'emp.xml');
  dbms_lob.createTemporary(clobVar, FALSE);
  dbms_lob.open(bfVar, dbms_lob.lob_readonly);
  dbms_lob.loadFromFile(dest_lob => clobVar,
                        src_lob  => bfVar,
                        amount   => dbms_lob.getLength(bfVar));
  dbms_lob.close(bfVar);

  -- create parser handle
  pVar := dbms_xmlparser.newParser;

  -- parse the document
  dbms_xmlparser.parseClob(pVar, clobVar);
  -- create a new DOM document
  DOMDoc := dbms_xmlparser.getDocument(pVar);

  -- free resources associated with the CLOB and Parser
  dbms_lob.freeTemporary(clobVar);
  dbms_xmlparser.freeParser(pVar);

  -- generate a list of EMP nodes in the DOM document
  DomNL := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(DOMDoc), '/EMPLOYEES/EMP');

  -- load into an array for insert from the DOM document
  FOR cur_emp IN 0 .. dbms_xmldom.getLength(DomNL) - 1 LOOP
    DomN := dbms_xmldom.item(DomNL, cur_emp);
    -- increase the array size by 1 for each loop
    emptab_t.extend;

    -- use XPATH syntax to assign values to the collection elements
    dbms_xslprocessor.valueOf(DomN, 'EMPNO/text()', emptab_t(emptab_t.last).empno);
    dbms_xslprocessor.valueOf(DomN, 'ENAME/text()', emptab_t(emptab_t.last).ename);
    dbms_xslprocessor.valueOf(DomN, 'JOB/text()', emptab_t(emptab_t.last).job);
    dbms_xslprocessor.valueOf(DomN, 'MGR/text()', emptab_t(emptab_t.last).mgr);
    dbms_xslprocessor.valueOf(DomN, 'HIREDATE/text()', emptab_t(emptab_t.last).hiredate);
    dbms_xslprocessor.valueOf(DomN, 'SAL/text()', emptab_t(emptab_t.last).sal);
    dbms_xslprocessor.valueOf(DomN, 'COMM/text()', emptab_t(emptab_t.last).comm);
    dbms_xslprocessor.valueOf(DomN, 'DEPTNO/text()', emptab_t(emptab_t.last).deptno);

    -- optionally access an element for validation
    dbms_output.put_line(emptab_t(emptab_t.COUNT).empno);
  END LOOP;

  -- perform an array insert of the XML into the relational table
  FORALL i IN emptab_t.FIRST .. emptab_t.LAST
  INSERT INTO emp VALUES emptab_t(i);

  COMMIT;

  -- release resources
  dbms_xmldom.freeDocument(DOMDoc);
EXCEPTION
  WHEN OTHERS THEN
    dbms_lob.freetemporary(clobVar);
    dbms_xmlparser.freeParser(pVar);
    dbms_xmldom.freeDocument(DOMDoc);
END;
/

Related Topics
DBMS_LOB
DBMS_XMLGEN
DBMS_XMLSTORE
DBMS_XSLPROCESSOR

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