Oracle XMLTable Tutorial With Example

Since Oracle 10g, Oracle has added new functions XQuery and XMLTable to its arsenal of XML processing APIs. XMLQuery lets you construct XML data and query XML and relational data using the XQuery language. XMLTable lets you create relational tables and columns from XQuery query results.

In this post we will learn about Oracle XMLTable function. The best way to learn is to learn by example. This way you can quickly understand different aspect of the API.

So lets start with our example. Consider a table EMPLOYEES which holds some XML data. Below is the create statement this table.

CREATE TABLE EMPLOYEES (    id     NUMBER,    data   XMLTYPE );

Now the table is ready. Lets insert a record in it. Below INSERT statement add one record having some XML content in it.

INSERT INTO EMPLOYEES      VALUES (1, xmltype ('              John         Watson         30         johnwatson@sh.com

    

        Sherlock

        Homes

        32

        sherlock@sh.com

    

    

        Jim

        Moriarty

        52

        jim@sh.com

    

    

        Mycroft

        Holmes

        41

        mycroft@sh.com

    

'));

Notice the XML contains employee related data. Before we start lets check few facts from above xml.

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

Now we can use Oracle XMLTable function to retrieve different information from this XML.

Let’s get started…

1. Learning XPath Expressions

Before we start with Oracle XMLTable function it is good to know a bit about XPath. XPath uses a path expression to select nodes or list of node from a xml document. Heres a list of useful paths and expression that can be used to select any node/nodelist from a xml document.

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’

There are other useful expressions that you can use to query the data.

Read this w3school page for more details: http://www.w3schools.com/xpath/xpath_syntax.asp

2. Learning Basics of Oracle XMLTable function

Lets get started with Oracle XMLTable function. Below are few examples of using different expressions of XPath to fetch some information from xml document.

2.1 Read firstname and lastname of all employees

In this query, we using XMLTable function to parse the XML content from Employees table.

--print firstname and lastname of all employees    SELECT t.id, x.*      FROM employees t,           XMLTABLE ('/Employees/Employee'                     PASSING t.data                     COLUMNS firstname VARCHAR2(30) PATH 'firstname',                             lastname VARCHAR2(30) PATH 'lastname') x     WHERE t.id = 1;

Note the syntax of XMLTable function:

XMLTable(''          PASSING column>          COLUMNS column name> <column type> PATH )

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.

Output:

2.2 Read node value using text()

In above example we read the content of node firstname / lastname. 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.

--print firstname of all employees    SELECT t.id, x.*      FROM employees t,           XMLTABLE ('/Employees/Employee/firstname'                     PASSING t.data                     COLUMNS firstname VARCHAR2 (30) PATH 'text()') x     WHERE t.id = 1;

Output:

Along with text() expression, Oracle provides various other useful expressions. For example item(), node(), attribute(), element(), document-node(), namespace(), text(), xs:integer, xs:string.

2.3 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.

--print employee type of all employees    SELECT emp.id, x.*      FROM employees emp,           XMLTABLE ('/Employees/Employee'                     PASSING emp.data                     COLUMNS firstname VARCHAR2(30) PATH 'firstname',                             type VARCHAR2(30) PATH '@type') x;

Output:

2.3 Read specific employee record using employee id

--print firstname and lastname of employee with id 2222    SELECT t.id, x.*      FROM employees 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;

Output:

2.4 Read firstname lastname of all employees who are admins

--print firstname and lastname of employees who are admins    SELECT t.id, x.*      FROM employees 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;

Output:

2.5 Read firstname lastname of all employees who are older than 40 year

--print firstname and lastname of employees having age > 40    SELECT t.id, x.*      FROM employees 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;

Output: