Functions for constructing XML values

Several SQL/XML publishing functions can be used together to publish relational data in XML format.

Those functions are:

XMLAGG aggregate function
Returns an XML sequence containing an item for each non-null value in a set of XML values.
XMLATTRIBUTES scalar function
Constructs XML attributes from the arguments. This function can only be used as an argument of the XMLELEMENT function.
XMLCOMMENT scalar function
Returns an XML value with a single comment node with the input argument as the content.
XMLCONCAT scalar function
Returns a sequence containing the concatenation of a variable number of XML input arguments.
XMLDOCUMENT scalar function
Returns an XML value with a single document node with zero or more children nodes.

This function creates a document node, which by definition, every XML document must have. A document node is not visible in the textual representation of XML, however, every document that is to be stored in a Db2 table must contain a document node.

It is not necessary to call XMLDOCUMENT when you insert an XML value into an XML column, or update an XML column with an XML value. Db2 implicitly adds the document node for you. For example, the following INSERT statement explicitly calls XMLDOCUMENT to insert value COL2:

INSERT INTO T1 (INT1,XML1)                     
SELECT X.COL1, XMLDOCUMENT(X.COL2)             
FROM XMLTABLE('/A/B' PASSING CAST (? AS XML)   
       COLUMNS COL1 INTEGER PATH '@id',        
               COL2 XML PATH '.') X;

However, you can omit XMLDOCUMENT, and Db2 implicitly adds the document node for COL2:

INSERT INTO T1 (INT1,XML1)                     
SELECT X.COL1, X.COL2             
FROM XMLTABLE('/A/B' PASSING CAST (? AS XML)   
       COLUMNS COL1 INTEGER PATH '@id',        
               COL2 XML PATH '.') X;
XMLELEMENT scalar function
Returns an XML value that is an XML element node.
XMLFOREST scalar function
Returns an XML value that is a sequence of XML element nodes.
XMLNAMESPACES declaration
Constructs namespace declarations from the arguments. This declaration can only be used as an argument of the XMLELEMENT or XMLFOREST functions.
XMLPI scalar function
Returns an XML value with a single processing instruction node.
XMLTEXT scalar function
Returns an XML value with a single text node having the input argument as the content.

You can combine these functions to construct XML values that contain different types of nodes. You need to specify the functions in the order in which you want the corresponding elements to appear.

Example 1

Suppose that you want to construct the following document, which has constant values: 
<elem1 xmlns="http://posample.org" id="111">
  <!-- example document -->
  <child1>abc</child1>
  <child2>def</child2>
</elem1>
The document consists of:
  • Three element nodes (elem1, child1, and child2)
  • A namespace declaration
  • An id attribute on elem1
  • A comment node
To construct this document, you need to invoke publishing functions in the following order:
  1. Create an element node named elem1, using XMLELEMENT.
  2. Add a default namespace declaration to the XMLELEMENT function call for elem1, using XMLNAMESPACES.
  3. Create an attribute named id using XMLATTRIBUTES, placing it after the XMLNAMESPACES declaration.
  4. Create a comment node using XMLCOMMENT, within the XMLELEMENT function call for elem1.
  5. Create a sequence of element nodes that are named child1 and child2, using the XMLFOREST function, within the XMLELEMENT function call for elem1.
The following SELECT statement constructs the document: 
SELECT XMLELEMENT (NAME "elem1",
  XMLNAMESPACES (DEFAULT 'http://posample.org'),
  XMLATTRIBUTES ('111' AS "id"),
  XMLCOMMENT (' example document '),
  XMLFOREST('abc' as "child1",
  'def' as "child2"))
  FROM SYSIBM.SYSDUMMY1

Example 2

Suppose that you want to construct an XML document from name elements in the Description column of the sample Product table.

The documents in the Description column look similar to this one: 
<product xmlns="http://posample.org" pid="100-100-01"> 
 <description>                                            
  <name>Snow Shovel, Basic 22"</name>                     
  <details>Basic Snow Shovel, 22" wide, straight handle with D-Grip</details>
  <price>9.99</price>                                     
  <weight>1 kg</weight>                                   
 </description>                                           
</product>
You want the constructed document to look like this: 
<allProducts xmlns="http:⁄⁄posample.org">
  <item>Snow Shovel, Basic 22"<⁄item>
  <item>Snow Shovel, Deluxe 24"<⁄item>
  <item>Snow Shovel, Super Deluxe 26"<⁄item>
  <item>Ice Scraper, Windshield 4" Wide<⁄item>
<⁄allProducts>
The document consists of:
  • Five element nodes (allProducts, and four item elements)
  • A namespace declaration
To construct this document, you need to invoke publishing functions in the following order:
  1. Create an element node named allProducts, using XMLELEMENT.
  2. Add a default namespace declaration to the XMLELEMENT function call for allProducts, using XMLNAMESPACES.
  3. Create a sequence of element item nodes, using the XMLELEMENT function within the XMLAGG function call.
The following SELECT statement constructs the document: 
SELECT XMLELEMENT (NAME "allProducts",
                   XMLNAMESPACES (DEFAULT 'http://posample.org'),
                   XMLAGG(XMLELEMENT (NAME "item", p.name)))
FROM Product p

Example 3

Suppose that you want to construct an XML document from name elements in documents in the Description column of the sample Product table, and Quantity column values of the sample Inventory table. The join column for the Product and Quantity tables is the Pid column.

You want the generated XML document to look like this: 
<saleProducts xmlns="http:⁄⁄posample.org">
  <prod id="100-100-01">
    <name>Snow Shovel 22"<⁄name>
    <numInStock>5<⁄numInStock>
  <⁄prod>
  <prod id="100-101-01">
    <name>Snow Shovel 24"<⁄name>
    <numInStock>25<⁄numInStock>
  <⁄prod>
  <prod id="100-103-01">
    <name>Deluxe Snow Shovel 26"<⁄name>
    <numInStock>55<⁄numInStock>
  <⁄prod>
  <prod id="100-201-01">
    <name>Ice Scraper 4"<⁄name>
    <numInStock>99<⁄numInStock>
  <⁄prod>
<⁄saleProducts>
The document consists of:
  • Thirteen element nodes (saleProducts, and four prod elements, each of which contains a name element and a numInStock element)
  • a namespace declaration
To construct this document, you need to invoke publishing functions in the following order:
  1. Create an element node named saleProducts, using XMLELEMENT.
  2. Add a default namespace declaration to the XMLELEMENT function call for saleProducts, using XMLNAMESPACES.
  3. Create a sequence of element prod nodes, using the following function invocations within the XMLAGG function invocation to construct the prod nodes:
    1. XMLELEMENT, to create prod node
    2. XMLATTRIBUTES, to add an id attribute to each prod element
    3. XMLFOREST, to construct the name and numInStock elements
The following SELECT statement constructs the document: 
SELECT XMLELEMENT (NAME "saleProducts",
                   XMLNAMESPACES (DEFAULT 'http://posample.org'),
                   XMLAGG (XMLELEMENT (NAME "prod",
                           XMLATTRIBUTES (p.Pid AS "id"),
                           XMLFOREST (p.name as "name",
                                      i.quantity as "numInStock"))))
FROM PRODUCT p, INVENTORY i
WHERE p.Pid = i.Pid

Example 4

When you construct an XML value using XMLELEMENT or XMLFOREST, you might encounter NULL values in the source tables. You can use the EMPTY ON NULL and NULL ON NULL options of XMLELEMENT and XMLFOREST to specify whether to generate an empty element or no element when the functions encounter a NULL value. The default NULL handling for XMLELEMENT is EMPTY ON NULL. The default NULL handling for XMLFOREST is NULL ON NULL.

Suppose that the LOCATION column of the INVENTORY table contains a NULL value in one row. You want to construct elements from the LOCATION column that contain an empty sequence if the LOCATION value is NULL. The following SELECT statement does that: 
SELECT XMLELEMENT (NAME "newElem",
                   XMLATTRIBUTES (PID AS "prodID"),
                   XMLFOREST (QUANTITY as "quantity",
                              LOCATION as "loc" OPTION EMPTY ON NULL))
FROM INVENTORY