XMLTABLE

    The table contains two rows which look like this:    Row #1:  <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>    Row #2:  <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 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 doc 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 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 examples 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 (using the XPath expression) from the employee elements, which are produced by the row-generating XPath expression. The extracted values are 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 SQL/XML support included in Db2 for i offers a built in and standardized solution for working with XML from within SQL. XMLTABLE adds significant value by allowing SQL queries to query both XML and relational data in the same query. Working XML data within a relational model is often times a non-trivial task, XMLTABLE offers a high degree of flexibility by supporting a wide range of XPath step expressions, predicates, and built in functions that can be used within the row and column generating expressions.

A general overview for how to use XMLTABLE to reference XML content is available in the SQL XML Programmer's guide.

The SQL Reference - XMLTABLE table function contains complete syntax.