Oracle XMLTable Tutorial with Example

oracle-xmltable-example
Oracle XMLTable Tutorial

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 );
Code language: SQL (Structured Query Language) (sql)

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>'));
Code language: HTML, XML (xml)

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.

ExpressionDescription
nodenameSelects 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
employeeSelects all nodes with the name “employee”
employees/employeeSelects all employee elements that are children of employees
//employeeSelects 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 ExpressionResult
/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;
Code language: SQL (Structured Query Language) (sql)

Note the syntax of XMLTable function:

XMLTable('<XQuery>' PASSING <xml column> COLUMNS <new column name> <column type> PATH <XQuery path>)
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

Output:

2.4 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;
Code language: SQL (Structured Query Language) (sql)

Output:

2.5 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;
Code language: SQL (Structured Query Language) (sql)

Output:

2.6 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;
Code language: SQL (Structured Query Language) (sql)

Output:

Reference

Get our Articles via Email. Enter your email address.

You may also like...

40 Comments

  1. Bob Thompson says:

    Can you tell me how to select arbitrary xml tag names from an xml string, where the tag names are not known. Basically, I want to list all of the tags and their values. I’ve been searching for DAYS and I can’t find the answer!!!!!

    • Probably you know (can find) examples on how to find “the values”. The following list all elements and attributes (nb. performance intensive though)

      select *
        from table i
           , xmltable( '//* | //@*'
                       passing i.xmlcontent
                       columns 
                         rno for ordinality,
                         element_name varchar2(100) path 'name(.)'
                     ) x1
      

  2. Tom says:

    This is the clearest explanation I’ve found on how to use XMLTABLE. Thank you.

  3. Apollo says:

    Nice one Mr. Patel. It is such a nice explanation.

    • Sharky says:

      Hi!

      Just add this to the first parameter of xmltable:

      XMLNAMESPACES(‘http://namespace.hu’ AS “ns”),

  4. the mrs says:

    Very, very nice. Help me enormously. Thank you.

  5. Snehal Patel says:

    Great blog site Viral….Nice posts and very handy.

  6. udhayakumar says:

    wonderful explanation, superb post, thanks a lot

  7. Anandan says:

    Thanks. But How can i add namespaces if any?

  8. SONG says:

    this is excellent short tutorial for XML DB. allow people know it in 5 minutes. great! thanks,

  9. shrikant agrawal says:

    Very nice way to explain……… Thanks

  10. kirupagaran says:

    I got error while i am running 2.1 query rest of all the query got error. Please help me. error msg is ORA-01780: string literal required

  11. kirupagaran says:

    I got error while i am running 2.1 query rest of all the query got error. Please help me. error msg is ORA-01780: string literal required thanks kirupa

  12. Leo James says:

    Viral
    I have read Oracle documentation may be a day. Honestly there is no clue how to learn this quickly. You put a good documentation explaining clearly and presenting them neatly.

    Thanks for the work and sharing…

    Keep the good work…

    Leo James

  13. Eugene says:

    I created EMPLOYEE table and inserted the sample data for EMPLOYEE but
    when I try to run 2.1, 2.2, or any of the queries. I got “no rows selected”
    Could someone help?

  14. Eugene says:

    I created EMPLOYEES table and inserted the sample data. If I run select * from EMPLOYEES then I will see the result in ID and XML data.
    But when I ran any of 2.1, 2.2, etc…queries then I got “no rows selected”.
    Can someone told me what’s the issue? Thanks!
    Eugene

  15. Josef says:

    I would like to add questiong for topic “list of all elements and theirs values”. I have created script as
    SELECT *
    FROM personal_data i,
    XMLTABLE( ‘//* | //@*’
    PASSING i.person_data
    COLUMNS row_number FOR ORDINALITY,
    element_name varchar2(100) PATH ‘name(.)’,
    element_value varchar2(100) PATH ‘text()’
    ) lines
    ;
    column “person data” contain your xml employees examples. Script correctly returns element_name and element_value in case of element. In case of attribute no element_value is returned (emplid and type). Please could you help me?
    h a n d.

  16. Jens says:

    I did the same as Eugene, created EMPLOYEE table and inserted the sample data for EMPLOYEE and see no results. What is the error ?

    Verbunden mit:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SQL> select * FROM employees;
    
            ID
    ----------
    DATA
    --------------------------------------------------------------------------------
             1
    <employees>
      <employee emplid="1111" type="admin">
        <firstname>John</firstna
    
    
    SQL> SELECT t.id, x.*
      2       FROM employees t,
      3            XMLTABLE ('/Employees/Employee'
      4                      PASSING t.data
      5                      COLUMNS firstname VARCHAR2(30) PATH 'firstname',
      6                              lastname VARCHAR2(30) PATH 'lastname') x
      7      WHERE t.id = 1
      8      ;
    
    Es wurden keine Zeilen ausgewõhlt
    
    SQL>

  17. Jens says:

    Found the solution by myself. :)
    XML-Tags are case sensitive ‘/Employees/Employee’ will not fiind anything,
    use ‘/employees/employee’ instead. The same notation as in the inserted XML.

    • Sona says:

      Hi, very useful resource.

  18. david says:

    Hi, very useful resource. Thanks for posting.

  19. Nicki says:

    Fantastic post.
    Another question please.
    When I use the element_name, element_value query it works perfectly but I need 1 row per element not multiple rows. Is there any way to do that without specifying each column? Trying to do this in a view

  20. Raj says:

    Please let me know how to retrieve the parent of the current node in oracle? .. is not working

  21. Edu says:

    Hi,
    How do I use dynamic variable to a value of an attribute?

    Ej : /Employees/Employee[@type= ‘ || MIVARIABLE || ‘];

    Thanks!!!

  22. Speake says:

    perfect Thanks!!!
    :D

  23. peps says:

    Hi, there

    Great tutorial, however; the insert isn’t working with an XML file with 16000 + records.
    What is the maximum number of characters for the “XMLTYPE” data type?
    Is there a workaround for this?

  24. peps says:

    Hi, there

    Great tutorial, however, the insert isn’t working with an XML file with 16000 + records.
    What is the maximum number of characters for the “XMLTYPE” data type?
    Is there a workaround for this??

    — Sorry, had a typo in my email address in the previous post

  25. Mike says:

    Hi Mr. Patel, thanks so much for this, I was blundering about for hours trying to make sense of the Oracle documentation on this and getting nowhere, but thanks to your very clear examples I was able to do what I wanted in about 5 minutes!

    Keep up the good work!

  26. Alireza Rahimi says:

    Hello,

    how is it work, if i have namespaces.

    thanks Alireza Rahimi

  27. anonymous says:

    nice one

  28. Chinmay Das says:

    Hi ,
    I have one question on this
    //employee[@type=’admin’] Selects all the employee elements that have an attribute named type with a value of ‘admin’

    if I want to search like ‘%admin%’ how to handle this in my query ?

  29. umit says:

    hi…. please help me.
    I want one select statement in plSql that show me bellow result :
    name depType
    —————-
    sara master
    ———————————————-

    sara

  30. umit says:

    hi
    how i can retrive attribite of parent tag in XML statement????
    because in can not make relation between 2 master-detail table in oracle after in insert data from XML file into them

  31. Charter says:

    What to do if I want to use this in SQL Server it’s saying XMLTABLE as invalid object what can be replaced to make it work.
    Early revert is highly appreciated.

  32. Muneer says:

    Thank you so much Viral. This post helped me.

  33. Hany says:

    Hi,
    The XML data I’m trying to query has got a different format like:
    COMPLETED

    I need to get the value “COMPLETED” but the namespace next to the “result” tag confuses XMLQUERY function to get the value. Any hints are highly appreciated.
    BR
    Hany

  34. Francois says:

    Posted back in 2014 and still, one of the best XMLTable tutorials available, thank you!

  35. Bartek says:

    The best and simplest XMLTable tutorial !!!

    You just ommit one thing. If you use namespaces, so you have to add to XMLTABLE such line:

    XMLTABLE
    (XMLNAMESPACES (‘URL’ as “alias”),
    ‘/alias:NameOfParent/alias:NameOfChild’
    PASSING ….

  36. Kish says:

    Great! Thank you!

  37. Josh says:

    This tutorial helped me a lot of times. Tons of thanks from Malaysia!

Leave a Reply

Your email address will not be published. Required fields are marked *