www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

XML Support

Rendering SQL Queries as XML (FOR XML Clause)
FOR XML EXPLICIT Mode Examples of FOR XML Functions FOR XML Syntax
XML Composing Functions in SQL Statements (SQLX)
Virtuoso XML Services
Querying Stored XML Data
Using UpdateGrams to Modify Data
XML Templates
XML DTD and XML Schemas
XQuery 1.0 Support
XSLT Transformation
XMLType
Changing XML entities in DOM style

13.1. Rendering SQL Queries as XML (FOR XML Clause)

Virtuoso extends SQL-92 with the FOR XML clause that allows any SQL result set to be turned into XML according to some simple rules. The notation and functionality are similar to those offered by Microsoft SQL Server and IIS.

The FOR XML clause has 3 variants:

RAW

Make an XML entity from each row of the result set; do not attempt to construct hierarchies. Each row's data is enclosed in a <ROW/> element and each column is either an attribute or child element.

AUTO

A hierarchy is constructed with one level for each table of the join for which at least one column is selected. The table whose column is first mentioned in the selection will be the topmost element, the next table its child, etc. Each level of the tree will consist of one type of element. A parent element will have multiple children if consecutive rows do not differ in the column values coming from the parent element. When a table's column values differ from the previous row, the element and all children thereof are closed and a new element is started, with children filled out from other columns of the result set.

EXPLICIT

This mode gives more control on the resulting tree's structure while requiring a more elaborate query structure. In this mode, the query will be a UNION ALL of many joins and each row will specify exactly one element. Which type of element this is and where in the tree it will be placed are determined by the values of the first two columns, TAG and PARENT.

In all modes, columns may either be attributes or sub-elements. The ELEMENT keyword after the FOR XML clause forces all columns to be rendered as sub-elements; attribute are the default.

In all modes except explicit, the names of elements are the unprefixed table names and the names of attributes are the columns' names in the result set. If tables have correlation names the correlation names are used in the output instead of the table names. Expressions are allowed in the selections but these should be named using AS. In AUTO mode Virtuoso assumes expressions belong to the topmost element.

The FOR XML clause is generally allowed in SELECT statements in place of the FOR UPDATE clause. However it only has an effect when the statement is executed through the xml_auto() function.

See Also:

The SQL-XML Statements page described in the Visual Server Administration Interface section provides a fast graphical way of supplying an SQL statement to Virtuoso and saving the view as a resource accessible from the WebDAV store.

13.1.1. FOR XML EXPLICIT Mode

This mode gives the developer the most control over the generated result tree but requires a verbose query formulation. Each row must begin with two integer columns, the first identifying the element represented by the row and the second the parent element type of this element. Consider:

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;

This query makes a two level tree where Categories have Product children. The selection in the first UNION term specifies the element types in the result set. The two first columns, TAG and PARENT are required in all EXPLICIT queries. Subsequent columns have an extended AS declaration that specifies which element they belong to, what that element is called in XML and what the column will be called. A row where TAG has a value of 1 will pick the columns which has [xxx!1!yyy] as their alias; rows with a TAG of 2 will pick columns with an alias with [xxx!2!yyy] and so on.

If consecutive rows have a different TAG but the same PARENT, these will be siblings of different types. This possibility does not exist with the other FOR XML modes.

If the PARENT is 0 or NULL, then any previously open elements in the result are closed and the element of the row becomes a top-level element. When PARENT refers to the TAG of a presently open element in the set, all children of that element are closed and the row's element is inserted as the next child of the last element with the TAG equal to the new row's PARENT. All open tags are closed at the end of the result set.

Note

Since each level of the tree is generated by a different term in the UNION ALL, an ORDER BY will invariably be needed to group the children after their parents. If the parent rows have NULLs in place of the child row's key values, the parent gets sorted first because NULL collates first.


13.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:

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.

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.

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.


13.1.3. Functions

xml_auto()

13.1.4. FOR XML Syntax

for__xml ::= FOR XML <mode> [ ELEMENT ]

<mode> ::= RAW | AUTO | EXPLICIT

<explicit column> ::=  scalar_exp AS '[' <element> '!' <tag no> '!'
                   <column name> [ '!' <option> ] ']'

<tag no> ::= INTNUM

<column name> ::= IDENTIFIER

<element> ::= IDENTIFIER

<option> ::= IDENTIFIER

The <explicit column> should be used in the selection list of the first term of the UNION ALL construct in a FOR XML EXPLICIT query. Virtuoso provides this functionality separately from any Web server context, although these are principally expected to be used inside VSP pages.

The text of <option> part of the <explicit column> is ignored but if it is present then the value is placed into a sub-element of the element for the row, not into an attribute.