XMLTABLE example: Inserting values returned from XMLTABLE

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

For example, the following XML documents are stored in the sample CUSTOMER table:

<customerinfo xmlns="http:⁄⁄posample.org" 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 xmlns="http:⁄⁄posample.org" 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>

You want to insert values from these documents into a table with the following definition:

CREATE TABLE CUSTADDR (CUSTNAME VARCHAR(30),
                       CUSTSTREET VARCHAR(30),
                       CUSTCITY VARCHAR(30)
                       CUSTSTATE VARCHAR(30),
                       CUSTZIP VARCHAR(30))

The following INSERT statement, which uses XMLTABLE, populates CUSTADDR with values from the XML documents:

INSERT INTO CUSTADDR
  SELECT X.*
  FROM CUSTOMER,
  XMLTABLE (XMLNAMESPACES(DEFAULT 'http:⁄⁄posample.org'),
  '//customerinfo'
  PASSING CUSTOMER.INFO  
               COLUMNS
                   "CUSTNAME" VARCHAR(30) PATH 'name',
                   "CUSTSTREET" VARCHAR(30) PATH 'addr/street',
                   "CUSTCITY" VARCHAR(30) PATH 'addr/city',
                   "CUSTSTATE" VARCHAR(30) PATH 'addr/prov-state',
                   "CUSTZIP" VARCHAR(30) PATH 'addr/pcode-zip'
                ) as X

After you execute the INSERT statement, the CUSTADDR table looks like this:

Table 1. Contents of the CUSTADDR table after insert of a result table generated by XMLTABLE
CUSTNAME	CUSTSTREET	CUSTCITY	CUSTSTATE	CUSTZIP
Kathy Smith	25 EastCreek	Markham	Ontario	N9C 3T6
Robert Shoemaker	1596 Baseline	Aurora	Ontario	N8X-7F8