DBMS_XML Guide
DBMS_XML Guide.
Last updated:
Warning: Review and test in a non-production environment before running.
-- Based on: Oracle XML DB Developer's Guide – https://docs.oracle.com/en/database/oracle/oracle-database/19/adxdb/
-- and "Oracle XMLTable Tutorial with Example", ViralPatel.net – https://viralpatel.net/oracle-xmltable-tutorial/
DBMS_XMLDOM - For accessing XMLType objects.
DBMS_XMLPARSER - For accessing the contents and structure of XML documents.
DBMS_XSLPROCESSOR - For transforming XML documents to other formats using XSLT.
-----------------------------------------------------------------------------------------------------------------------
utl_file_dir - /disk01/utlfile_tmp
-----------------------------------------------------------------------------------------------------------------------
AL32UTF8 is the Oracle Database character set that is appropriate for XMLType data.
Do not use UTF8 for XML data.
UTF8 supports only Unicode version 3.1 and earlier; it does not support all valid XML characters.
Using database character set UTF8 for XML data could potentially stop a system or affect security negatively.
If a character that is not supported by the database character set appears in an input-document element name,
a replacement character (usually "?") will be substituted for it. This will terminate parsing and raise an exception.
It could cause a fatal error.
-----------------------------------------------------------------------------------------------------------------------
PL/SQL DOM API for XMLType (DBMS_XMLDOM) :
Remember to call procedure freeDocument for each DOMDocument instance, when you are through with the instance.
This procedure frees the document and all of its nodes.
CREATE TABLE person OF XMLType;
DECLARE
var XMLType;
doc DBMS_XMLDOM.DOMDocument;
ndoc DBMS_XMLDOM.DOMNode;
docelem DBMS_XMLDOM.DOMElement;
node DBMS_XMLDOM.DOMNode;
childnode DBMS_XMLDOM.DOMNode;
nodelist DBMS_XMLDOM.DOMNodelist;
buf VARCHAR2(2000);
BEGIN
var := XMLType('<PERSON><NAME>ramesh</NAME></PERSON>');
-- Create DOMDocument handle
doc := DBMS_XMLDOM.newDOMDocument(var);
ndoc := DBMS_XMLDOM.makeNode(doc);
DBMS_XMLDOM.writeToBuffer(ndoc, buf);
DBMS_OUTPUT.put_line('Before:'||buf);
docelem := DBMS_XMLDOM.getDocumentElement(doc);
-- Access element
nodelist := DBMS_XMLDOM.getElementsByTagName(docelem, 'NAME');
node := DBMS_XMLDOM.item(nodelist, 0);
childnode := DBMS_XMLDOM.getFirstChild(node);
-- Manipulate element
DBMS_XMLDOM.setNodeValue(childnode, 'raj');
DBMS_XMLDOM.writeToBuffer(ndoc, buf);
DBMS_OUTPUT.put_line('After:'||buf);
DBMS_XMLDOM.freeDocument(doc);
INSERT INTO person VALUES (var);
END;
/
-----------------------------------------------------------------------------------------------------------------------
CREATE TABLE MY_EMP
(
id NUMBER,
data XMLTYPE
);
INSERT INTO MY_EMP VALUES (1, xmltype ('<Employees>
<Employee emplid="1111" type="admin">
<firstname>John</firstname>
<lastname>Watson</lastname>
<age>30</age>
<email>johnwatson@sh.com</email>
</Employee>
<Employee emplid="2222" type="admin">
<firstname>Sherlock</firstname>
<lastname>Homes</lastname>
<age>32</age>
<email>sherlock@sh.com</email>
</Employee>
<Employee emplid="3333" type="user">
<firstname>Jim</firstname>
<lastname>Moriarty</lastname>
<age>52</age>
<email>jim@sh.com</email>
</Employee>
<Employee emplid="4444" type="user">
<firstname>Mycroft</firstname>
<lastname>Holmes</lastname>
<age>41</age>
<email>mycroft@sh.com</email>
</Employee>
</Employees>'));
1.There are 4 employees in our xml file
2.Each employee has a unique employee id defined by attribute emplid
3.Each employee also has an attribute type which defines whether an employee is admin or user.
4.Each employee has four child nodes: firstname, lastname, age and email
5.Age is a number
Expression Description
========== =====================================================================================================
nodename Selects all nodes with the name “nodename”
/ Selects from the root node
// Selects nodes in the document from the current node that match the selection no matter where they are
. Selects the current node
.. Selects the parent of the current node
@ Selects attributes
employee Selects all nodes with the name “employee”
employees/employee Selects all employee elements that are children of employees
//employee Selects all employee elements no matter where they are in the document
Below list of expressions are called Predicates.
The Predicates are defined in square brackets [ ... ].
They are used to find a specific node or a node that contains a specific value.
Path Expression Result
============================= ===========================================================================================
/employees/employee[1] Selects the first employee element that is the child of the employees element.
/employees/employee[last()] Selects the last employee element that is the child of the employees element
/employees/employee[last()-1] Selects the last but one employee element that is the child of the employees element
//employee[@type='admin'] Selects all the employee elements that have an attribute named type with a value of 'admin'
-----------------------------------------------------------------------------------------------------------------------
<Employees>
<Employee emplid="1111" type="admin">
<firstname>John</firstname>
<lastname>Watson</lastname>
<age>30</age>
<email>johnwatson@sh.com</email>
</Employee>
<Employee emplid="2222" type="admin">
<firstname>Sherlock</firstname>
<lastname>Homes</lastname>
<age>32</age>
<email>sherlock@sh.com</email>
</Employee>
<Employee emplid="3333" type="user">
<firstname>Jim</firstname>
<lastname>Moriarty</lastname>
<age>52</age>
<email>jim@sh.com</email>
</Employee>
<Employee emplid="4444" type="user">
<firstname>Mycroft</firstname>
<lastname>Holmes</lastname>
<age>41</age>
<email>mycroft@sh.com</email>
</Employee>
</Employees>
-----------------------------------------------------------------------------------------------------------------------
Note the syntax of XMLTable function:
-----------------------------------------------------------------------------------------------------------------------
XMLTable('<XQuery>'
PASSING <xml column>
COLUMNS <new column name> <column type> PATH <XQuery path>)
-----------------------------------------------------------------------------------------------------------------------
Read all data from employees :
-----------------------------------------------------------------------------------------------------------------------
SELECT t.id, x.*
FROM MY_EMP t,
XMLTABLE ('/Employees/Employee'
PASSING t.data
COLUMNS emplid VARCHAR2(30) PATH '@emplid',
type VARCHAR2(30) PATH '@type',
firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname',
age VARCHAR2(5) PATH 'age',
email VARCHAR2(75) PATH 'email') x
WHERE t.id = 1;
-----------------------------------------------------------------------------------------------------------------------
Read firstname and lastname of all employees :
-----------------------------------------------------------------------------------------------------------------------
SELECT t.id, x.*
FROM MY_EMP t,
XMLTABLE ('/Employees/Employee'
PASSING t.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname') x
WHERE t.id = 1;
The XMLTABLE function contains one row-generating XQuery expression and,
in the COLUMNS clause, one or multiple column-generating expressions.
In Listing 1, the row-generating expression is the XPath /Employees/Employee.
The passing clause defines that the emp.data refers to the XML column data of the table Employees emp.
The COLUMNS clause is used to transform XML data into relational data.
Each of the entries in this clause defines a column with a column name and a SQL data type.
In above query we defined two columns firstname and lastname
that points to PATH firstname and lastname or selected XML node.
-----------------------------------------------------------------------------------------------------------------------
Read node value using text() :
-----------------------------------------------------------------------------------------------------------------------
Sometimes you may want to fetch the text value of currently selected node item.
In below example we will select path /Employees/Employee/firstname.
And then use text() expression to get the value of this selected node.
Below query will read firstname of all the employees.
SELECT t.id, x.*
FROM MY_EMP t,
XMLTABLE ('/Employees/Employee/firstname'
PASSING t.data
COLUMNS firstname VARCHAR2 (30) PATH 'text()') x
WHERE t.id = 1;
-----------------------------------------------------------------------------------------------------------------------
Read Attribute value of selected node :
-----------------------------------------------------------------------------------------------------------------------
We can select an attribute value in our query. The attribute can be defined in XML node.
In below query we select attribute type from the employee node.
SELECT emp.id, x.*
FROM MY_EMP emp,
XMLTABLE ('/Employees/Employee'
PASSING emp.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
type VARCHAR2(30) PATH '@type') x;
-----------------------------------------------------------------------------------------------------------------------
Read specific employee record using employee id :
-----------------------------------------------------------------------------------------------------------------------
SELECT t.id, x.*
FROM MY_EMP t,
XMLTABLE ('/Employees/Employee[@emplid=2222]'
PASSING t.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname') x
WHERE t.id = 1;
-----------------------------------------------------------------------------------------------------------------------
Read firstname lastname of all employees who are admins :
-----------------------------------------------------------------------------------------------------------------------
SELECT t.id, x.*
FROM MY_EMP t,
XMLTABLE ('/Employees/Employee[@type="admin"]'
PASSING t.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname') x
WHERE t.id = 1;
-----------------------------------------------------------------------------------------------------------------------
Read firstname lastname of all employees who are older than 40 year :
-----------------------------------------------------------------------------------------------------------------------
SELECT t.id, x.*
FROM MY_EMP t,
XMLTABLE ('/Employees/Employee[age>40]'
PASSING t.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname',
age VARCHAR2(30) PATH 'age') x
WHERE t.id = 1;