Oracle XMLTable Tutorial with Example
- By Viral Patel on January 7, 2014
Since Oracle 10g, Oracle has added new functions
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 firstname.lastname@example.org Sherlock Homes 32 email@example.com Jim Moriarty 52 firstname.lastname@example.org Mycroft Holmes 41 email@example.com
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
- Each employee also has an attribute
typewhich defines whether an employee is admin or user.
- Each employee has four child nodes:
- 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.
||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 all nodes with the name “employee”|
||Selects all employee elements that are children of employees|
||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.
||Selects the first employee element that is the child of the employees element.|
||Selects the last employee element that is the child of the employees element|
||Selects the last but one employee element that is the child of the employees element|
||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:
' PASSING COLUMNS 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.
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;
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;
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;
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;
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;
Get our Articles via Email. Enter your email address.