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 NAME column in the PRODUCT table and the QUANTITY column in the INVENTORY table.
All <prod> elements are then aggregated within the constructed <saleProducts> element.

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

The previous query yields the following XML document:

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