IBM Support

XMLTABLE

News


Abstract

The addition of XMLTABLE support to Db2 for i users makes it easier for data centers to balance and extract value from a hybrid data model where XML data and relational data coexist.

Content

 

Assume you have created a table with an XML column create table emp (doc XML); Figure 1. XML to Relational
image-20200115124715-1
    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.

Table 1. XMLTABLE example
 
EMPID FIRSTNAME LASTNAME
The result of the previous query
901 John Doe
902 Peter Pan
903 Mary Jones



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.

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
15 January 2020

UID

ibm11167424