DB2 Version 9.7 for Linux, UNIX, and Windows

Example: Construct an XML document with values from multiple tables

This example shows how you can construct XML values suitable for publishing from multiple tables with SQL/XML publishing functions.

This example shows how an XML document can be constructed from values stored in multiple tables. In the following query, <prod> elements are constructed from a forest of elements, which are called name and numInStock, using the XMLFOREST function. This forest is built with values from the PRODUCT and INVENTORY tables. All <prod> elements are then aggregated within the constructed <saleProducts> element.

SELECT XMLELEMENT (NAME "saleProducts",
                   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

The previous query yields the following XML document:

<saleProducts>
  <prod id="100-100-01">
    <name>Snow Shovel, Basic 22 inch<⁄name>
    <numInStock>5<⁄numInStock>
  <⁄prod>
  <prod id="100-101-01">
    <name>Snow Shovel, Deluxe 24 inch<⁄name>
    <numInStock>25<⁄numInStock>
  <⁄prod>
  <prod id="100-103-01">
    <name>Snow Shovel, Super Deluxe 26 inch<⁄name>
    <numInStock>55<⁄numInStock>
  <⁄prod>
  <prod id="100-201-01">
    <name>Ice Scraper, Windshield 4 inch<⁄name>
    <numInStock>99<⁄numInStock>
  <⁄prod>
<⁄saleProducts>