XMLTABLE example: Specifying a default value for a column in the result table

You can specify a default value for any column in the XMLTABLE result table by using a DEFAULT clause. The default value is used if the XQuery expression that defines the column returns an empty sequence.

For example, the following XML documents are stored in the sample CUSTOMER table. Neither document has an age element.

<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 can use a query like this to create a result table in which the column value is "***No age***" if a document has no age for a customer:

SELECT X.*
  FROM CUSTOMER C, XMLTABLE (XMLNAMESPACES(DEFAULT 'http://posample.org'),
  '$cust/customerinfo' PASSING C.INFO as "cust"
  COLUMNS "CUSTNAME" VARCHAR(30) PATH './name',
  "AGE" VARCHAR(30) PATH './age' DEFAULT '***No age***') AS X
  WHERE CID=1001 OR CID=1003

The result table of the SELECT statement is:

Table 1. Result table from a query in which XMLTABLE has a default value for an item
CUSTNAME	AGE
Kathy Smith	*No age*
Robert Shoemaker	*No age*