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