Example: Inserting values returned from XMLTABLE

The XMLTABLE SQL table function can be used to retrieve values from within stored XML documents, which can then be inserted into a table.

This technique is a simple form of decomposition, where decomposition is the process of storing fragments of an XML document in columns of relational tables. (A more general type of decomposition is available with the annotated XML schema decomposition functionality. With annotated XML schema decomposition, you can decompose multiple XML documents at once into multiple tables.)

For example, if the following two XML documents were stored in a table named CUSTOMER:

<customerinfo Cid="1001">
  <name>Kathy Smith</name>
  <addr country="Canada"> 
    <street>25 EastCreek</street>
    <city>Markham</city> 
    <prov-state>Ontario</prov-state> 
    <pcode-zip>N9C 3T6</pcode-zip>
  </addr> 
  <phone type="work">905-555-7258</phone>
</customerinfo>  

<customerinfo Cid="1003">
  <name>Robert Shoemaker</name>
    <addr country="Canada">
    <street>1596 Baseline</street>
    <city>Aurora</city>
    <prov-state>Ontario</prov-state>
    <pcode-zip>N8X 7F8</pcode-zip>
  </addr>
  <phone type="work">905-555-7258</phone>
  <phone type="home">416-555-2937</phone>
  <phone type="cell">905-555-8743</phone>
  <phone type="cottage">613-555-3278</phone>
</customerinfo>

and you wanted to insert values from these documents into a table defined as:

CREATE TABLE CUSTPHONE (custname char(30), numbers XML)

then the following INSERT statement using XMLTABLE populates CUSTPHONE with values from the XML documents:

INSERT INTO CUSTPHONE
  SELECT X.*
  FROM XMLTABLE ('db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo'
                 COLUMNS
                   "CUSTNAME" CHAR(30) PATH 'name',
                   "PHONENUM" XML PATH 'document{<allphones>{phone}</allphones>}'
                ) as X

Notice that the XQuery node constructor function "document{<allphones>{phone}</allphones>}" is specified in the path expression for the PHONENUM column in XMLTABLE. The document constructor is needed because values inserted into XML columns (the NUMBERS column, in this case), must be well-formed XML documents. In this example, all <phone> elements for the <customerinfo> document with Cid="1003" are returned in a single sequence that contains four items:

{<phone type="work">905-555-7258</phone>,<phone type="home">416-555-2937</phone>,
<phone type="cell">905-555-8743</phone>, <phone type="cottage">613-555-3278</phone>}

This sequence on its own is not a well-formed XML document, and so cannot be inserted into the NUMBERS XML column. To ensure that the phone values are successfully inserted, all items of the sequence are constructed into a single well-formed document.

The resulting table is as follows (the output has been formatted for clarity):

Table 1. Result table
CUSTNAME	NUMBER
Kathy Smith	<allphones> <phone type="work">905-555-7258</phone> </allphones>
Robert Shoemaker	<allphones> <phone type="work">905-555-7258</phone> <phone type="home">416-555-2937</phone> <phone type="cell">905-555-8743</phone> <phone type="cottage">613-555-3278</phone> </allphones>