Example: Construct an XML document with values from a single table

This example shows how you can construct XML values suitable for publishing from a single table with SQL/XML publishing functions.

This example shows how an XML document can be constructed from values stored in a single table. In the following query:

Each <item> element is constructed with values from the NAME column of the PRODUCT table, using the XMLELEMENT function.
All <item> elements are then aggregated, using XMLAGG, within the constructed <allProducts> element.
A namespace is added to the <allProducts> element, with the XMLNAMESPACES function.

SELECT XMLELEMENT (NAME "allProducts",
                   XMLNAMESPACES (DEFAULT 'http://posample.org'),
                   XMLAGG(XMLELEMENT (NAME "item", p.name)))
FROM Product p

This query returns the following XML value. It is formatted here to improve readability.

<allProducts xmlns="http:⁄⁄posample.org">
  <item>Snow Shovel, Basic 22 inch<⁄item>
  <item>Snow Shovel, Deluxe 24 inch<⁄item>
  <item>Snow Shovel, Super Deluxe 26 inch<⁄item>
  <item>Ice Scraper, Windshield 4 inch<⁄item>
<⁄allProducts>

You can construct a similar XML document that contains a sequence of row elements by using the XMLROW function instead of aggregating the elements with XMLAGG. Item elements are also given a namespace prefix:

SELECT XMLELEMENT (NAME "products",
                   XMLNAMESPACES ('http://posample.org' AS "po"),
                   XMLROW(NAME AS "po:item"))
FROM Product

The resulting output is as follows:

<products xmlns:po="http://posample.org">
   <row>
      <po:item>Snow Shovel, Basic 22 inch</po:item>
   </row>
</products>
<products xmlns:po="http://posample.org">
   <row>
      <po:item>Snow Shovel, Deluxe 24 inch</po:item>
   </row>
</products>
<products xmlns:po="http://posample.org">
      <row><po:item>Snow Shovel, Super Deluxe 26 inch</po:item>
   </row>
</products>
<products xmlns:po="http://posample.org">
      <row><po:item>Ice Scraper, Windshield 4 inch</po:item>
   </row>
</products>