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 (' < Employees > < Employee emplid = "1111" type = "admin" > < firstname > John </ firstname > < lastname > Watson </ lastname > < age > 30 </ age > < email > [email protected] </ email > </ Employee > < Employee emplid = "2222" type = "admin" > < firstname > Sherlock </ firstname > < lastname > Homes </ lastname > < age > 32 </ age > < email > [email protected] </ email > </ Employee > < Employee emplid = "3333" type = "user" > < firstname > Jim </ firstname > < lastname > Moriarty </ lastname > < age > 52 </ age > < email > [email protected] </ email > </ Employee > < Employee emplid = "4444" type = "user" > < firstname > Mycroft </ firstname > < lastname > Holmes </ lastname > < age > 41 </ age > < email > [email protected] </ email > </ Employee > </ Employees > '));

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

There are 4 employees in our xml file Each employee has a unique employee id defined by attribute emplid Each employee also has an attribute type which defines whether an employee is admin or user. Each employee has four child nodes: firstname , lastname , age and email 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.

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('<XQuery>' PASSING <xml column> COLUMNS <new column name> <column type> PATH <XQuery 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.

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.

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.4 Read specific employee record using employee id

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.5 Read firstname lastname of all 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.6 Read firstname lastname of all employees who are older than 40 year

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:

Reference