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
<elem1 xmlns="http://posample.org" id="111">
<!-- example document -->
<child1>abc</child1>
<child2>def</child2>
</elem1>
- Three element nodes (
elem1
,child1
, andchild2
) - A namespace declaration
- An
id
attribute onelem1
- A comment node
- Create an element node named
elem1
, using XMLELEMENT. - Add a default namespace declaration to the XMLELEMENT function call for
elem1
, using XMLNAMESPACES. - Create an attribute named
id
using XMLATTRIBUTES, placing it after the XMLNAMESPACES declaration. - Create a comment node using XMLCOMMENT, within the XMLELEMENT function call for
elem1
. - Create a sequence of element nodes that are named
child1
andchild2
, using the XMLFOREST function, within the XMLELEMENT function call forelem1
.
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.
<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>
<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>
- Five element nodes (
allProducts
, and fouritem
elements) - A namespace declaration
- Create an element node named
allProducts
, using XMLELEMENT. - Add a default namespace declaration to the XMLELEMENT function call for
allProducts
, using XMLNAMESPACES. - Create a sequence of element
item
nodes, using the XMLELEMENT function within the XMLAGG function call.
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.
<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>
- Thirteen element nodes (
saleProducts
, and fourprod
elements, each of which contains aname
element and anumInStock
element) - a namespace declaration
- Create an element node named
saleProducts
, using XMLELEMENT. - Add a default namespace declaration to the XMLELEMENT function call for
saleProducts
, using XMLNAMESPACES. - Create a sequence of element
prod
nodes, using the following function invocations within the XMLAGG function invocation to construct theprod
nodes:- XMLELEMENT, to create
prod
node - XMLATTRIBUTES, to add an
id
attribute to eachprod
element - XMLFOREST, to construct the
name
andnumInStock
elements
- XMLELEMENT, to create
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.
SELECT XMLELEMENT (NAME "newElem",
XMLATTRIBUTES (PID AS "prodID"),
XMLFOREST (QUANTITY as "quantity",
LOCATION as "loc" OPTION EMPTY ON NULL))
FROM INVENTORY