Using XMLTABLE to reference XML content as a relational table

The XMLTABLE built-in table function can be used to retrieve the content of an XML document as a result set that can be referenced in SQL.

Assume you have a table called EMP with an XML column defined like this:

CREATE TABLE EMP (DOC XML)

The table contains 2 rows, which look like this:

<dept bldg="101">
  <employee id="901">
    <name>
      <first>John</first>
      <last>Doe</last>
    </name>
    <office>344</office>
    <salary currency="USD">55000</salary>
  </employee>
  <employee id="902">
    <name>
      <first>Peter</first>
      <last>Pan</last>
    </name>
    <office>216</office>
    <phone>905-416-5004</phone>
  </employee>
</dept>

<dept bldg="114">
  <employee id="903">
    <name>
      <first>Mary</first>
      <last>Jones</last>
    </name>
    <office>415</office>
    <phone>905-403-6112</phone>
    <phone>647-504-4546</phone>
    <salary currency="USD">64000</salary>
  </employee>
</dept>

In the XMLTABLE function invocation, you specify a row-generating XPath expression and, in the columns clause, one or more column-generating expressions. In this example, the row-generating expression is the XPath expression $d/dept/employee. The passing clause indicates that the variable $d refers to the XML column doc of the table emp.

SELECT X.*
  FROM emp,
       XMLTABLE ('$d/dept/employee' PASSING emp.doc AS "d"
                 COLUMNS
                      empID     INTEGER     PATH '@id',
                      firstname VARCHAR(20) PATH 'name/first',
                      lastname  VARCHAR(25) PATH 'name/last') AS X

The row-generating expression is applied to each XML document in the XML column and produces one or multiple employee elements (sub-trees) per document. The output of the XMLTABLE function contains one row for each employee element. Hence, the output produced by the row-generating XPath expression determines the cardinality of the result set of the SELECT statement.

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 an SQL data type. In the example above, the returned rows have 3 columns named empID, firstname, and lastname of data types Integer, Varchar(20), and Varchar(25), respectively. The values for each column are extracted from the employee elements, which are produced by the row-generating XPath expression, and cast to the SQL data types. For example, the path name/first is applied to each employee element to obtain the value for the column firstname. The row-generating expression provides the context for the column-generating expressions. In other words, you can typically append a column-generating expression to the row-generating expression to get an idea of what a given XMLTABLE function returns for a column.

The result of the previous query is:

EMPID       FIRSTNAME            LASTNAME
----------- -------------------- -------------------------
        901 John                 Doe
        902 Peter                Pan
        903 Mary                 Jones

Be aware that the path expressions in the COLUMNS clause must not return more than one item per row. If a path expression returns a sequence of two or more items, the XMLTABLE execution will typically fail, as it is not possible to convert a sequence of XML values into an atomic SQL value.