Prev Up Next
15.1.1.FOR XML EXPLICIT Mode Home 15.1.3.Functions

15.1.2.Examples of FOR XML

This section gives one example of each mode of FOR XML combined with the xml_auto() function to help us display the results simply. First we create a procedure that enables us to supply SQL and return XML using the xml_auto() function.

create procedure xmla (in q varchar)
{
  declare st any;
  st := string_output ();
  xml_auto (q, vector (), st);
  result_names (q);
  result (string_output_string (st));
}

Now we can apply this to a couple of examples:

Example15.1.XML RAW 

xmla ('select "category"."CategoryID", "CategoryName",
    "ProductName", "ProductID"
    from "Demo".."Categories" "category", "Demo".."Products" as "product"
    where "product"."CategoryID" = "category"."CategoryID" FOR XML RAW');

<ROW CategoryID="1" CategoryName="Beverages" ProductName="Chai" ProductID="1">
</ROW>
<ROW CategoryID="1" CategoryName="Beverages" ProductName="Chang" ProductID="2">
</ROW>
<ROW CategoryID="1" CategoryName="Beverages" ProductName="Guaraná Fantástica" ProductID="24">
</ROW>
<ROW CategoryID="1" CategoryName="Beverages" ProductName="Sasquatch Ale" ProductID="34">
</ROW>
<ROW CategoryID="1" CategoryName="Beverages" ProductName="Steeleye Stout" ProductID="35">
</ROW>
<ROW CategoryID="1" CategoryName="Beverages" ProductName="Côte de Blaye" ProductID="38">
</ROW>
<ROW CategoryID="1" CategoryName="Beverages" ProductName="Chartreuse verte" ProductID="39">
</ROW>
<ROW CategoryID="1" CategoryName="Beverages" ProductName="Ipoh Coffee" ProductID="43">
</ROW>
<ROW CategoryID="1" CategoryName="Beverages" ProductName="Laughing Lumberjack Lager" ProductID="67">
</ROW>
.....

As we can see, RAW mode produces a simple row-by-row account of the data encased within the <ROW.../> tags. This is the simplest mode.

Example15.2.XML AUTO 

xmla ('select "category"."CategoryID", "CategoryName",
    "ProductName", "ProductID"
    from "Demo".."Categories" "category", "Demo".."Products" as "product"
    where "product"."CategoryID" = "category"."CategoryID" FOR XML AUTO ELEMENT');

<category>
 <CategoryID>1</CategoryID> <CategoryName>Beverages</CategoryName><product>
 <ProductName>Chai</ProductName> <ProductID>1</ProductID></product>
<product>
 <ProductName>Chang</ProductName> <ProductID>2</ProductID></product>
<product>
 <ProductName>Guaraná Fantástica</ProductName> <ProductID>24</ProductID></product>
<product>
 <ProductName>Sasquatch Ale</ProductName> <ProductID>34</ProductID></product>
<product>
 <ProductName>Steeleye Stout</ProductName> <ProductID>35</ProductID></product>
<product>
 <ProductName>Côte de Blaye</ProductName> <ProductID>38</ProductID></product>
<product>
 <ProductName>Chartreuse verte</ProductName> <ProductID>39</ProductID></product>
<product>
 <ProductName>Ipoh Coffee</ProductName> <ProductID>43</ProductID></product>
<product>
 <ProductName>Laughing Lumberjack Lager</ProductName> <ProductID>67</ProductID></product>
<product>
.....

In contrast to RAW mode, AUTO produces results that are more tree-like. Only one category element is used for each category, and that contains all the children of the category.

Example15.3.XML EXPLICIT 

xmla ('
select 1 as tag, null as parent,
       "CategoryID" as [category!1!cid],
       "CategoryName" as [category!1!name],
       NULL as [product!2!pid],
       NULL as [product!2!name!element]
from "Demo".."Categories"
union all
select 2, 1, "category" ."CategoryID", NULL, "ProductID", "ProductName"
    from "Demo".."Categories" "category", "Demo".."Products" as "product"
    where "product"."CategoryID" = "category"."CategoryID"
order by [category!1!cid], 5
FOR XML EXPLICIT');

<CATEGORY CID="1" NAME="Beverages">
<PRODUCT PID="1">
 <NAME>Chai</NAME></PRODUCT>
<PRODUCT PID="2">
 <NAME>Chang</NAME></PRODUCT>
<PRODUCT PID="24">
 <NAME>Guaraná Fantástica</NAME></PRODUCT>
<PRODUCT PID="34">
 <NAME>Sasquatch Ale</NAME></PRODUCT>
<PRODUCT PID="35">
 <NAME>Steeleye Stout</NAME></PRODUCT>
<PRODUCT PID="38">
 <NAME>Côte de Blaye</NAME></PRODUCT>
<PRODUCT PID="39">
 <NAME>Chartreuse verte</NAME></PRODUCT>
<PRODUCT PID="43">
 <NAME>Ipoh Coffee</NAME></PRODUCT>
<PRODUCT PID="67">
 <NAME>Laughing Lumberjack Lager</NAME></PRODUCT>
<PRODUCT PID="70">
 <NAME>Outback Lager</NAME></PRODUCT>
<PRODUCT PID="75">
 <NAME>Rhönbräu Klosterbier</NAME></PRODUCT>
<PRODUCT PID="76">
 <NAME>Lakkalikööri</NAME></PRODUCT>
</CATEGORY>
<CATEGORY CID="2" NAME="Condiments">
<PRODUCT PID="3">
.....

In this example, we specify precisely the tree structure we wish, and construct the EXPLICIT query to produce that tree. Many times programmers know what the resulting XML should look like but do not know how to get exactly what they want. FOR XML EXPLICIT can be very useful in these cases.

Prev Up Next
15.1.1.FOR XML EXPLICIT Mode Home 15.1.3.Functions