Oracle XMLTable Tutorial with Example

oracle-xmltable-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.
Oracle XMLTable Tutorial

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

Output:
oracle-xmltables-query2

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:
oracle-xmltables-query1
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:
oracle xmltable attributes

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:
oracle-xmltables-query3

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:
oracle-xmltables-query4

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:
oracle-xmltables-query5

Reference



21 Comments

  • Bob Thompson 25 February, 2014, 22:20

    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!!!!!

    • Marco Gralike 16 December, 2014, 19:06

      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
      
  • Tom 1 April, 2014, 19:21

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

  • Apollo 13 April, 2014, 15:19

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

    • Sharky 14 January, 2015, 18:12

      Hi!

      Just add this to the first parameter of xmltable:

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

  • the mrs 23 April, 2014, 18:20

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

  • Snehal Patel 31 May, 2014, 3:39

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

  • udhayakumar 1 July, 2014, 22:25

    wonderful explanation, superb post, thanks a lot

  • Anandan 2 September, 2014, 17:39

    Thanks. But How can i add namespaces if any?

  • SONG 17 September, 2014, 0:23

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

  • shrikant agrawal 11 December, 2014, 15:54

    Very nice way to explain……… Thanks

  • kirupagaran 3 January, 2015, 14:15

    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

  • kirupagaran 3 January, 2015, 14:17

    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

  • Leo James 16 January, 2015, 17:58

    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

  • Eugene 30 January, 2015, 0:31

    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?

  • Eugene 30 January, 2015, 1:39

    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

  • Josef 3 February, 2015, 19:17

    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.

  • Jens 7 February, 2015, 1:25

    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>
  • Jens 7 February, 2015, 1:49

    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.

  • david 11 March, 2015, 3:44

    Hi, very useful resource. Thanks for posting.

  • Nicki 13 March, 2015, 12:41

    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

Leave a Reply

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

Note

To post source code in comment, use [code language] [/code] tag, for example:

  • [code java] Java source code here [/code]
  • [code html] HTML here [/code]