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>