<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="13. XML Support" />
<meta name="dc.subject" content="13. XML Support" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="webandxml.html" title="Chapter Contents" />
<link rel="prev" href=".html" title="" />
<link rel="next" href="composingxmlinsql.html" title="XML Composing Functions in SQL Statements (SQLX)" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>13. XML Support</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="forxmlforsql" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>13. XML Support</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="webandxml.html">Chapter Contents</a> | <a class="link" href="webandxml.html" title="XML Support">Prev</a> | <a class="link" href="composingxmlinsql.html" title="XML Composing Functions in SQL Statements (SQLX)">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="webandxml.html">XML Support</a>
</div>
<br />
<div class="selected">
<a href="forxmlforsql.html">Rendering SQL Queries as XML (FOR XML Clause)</a>
<div>
<a href="#forxmlexplicmode" title="FOR XML EXPLICIT Mode">FOR XML EXPLICIT Mode</a>
<a href="#examplesofforxml" title="Examples of FOR XML">Examples of FOR XML</a>
<a href="#forxmlfunc" title="Functions">Functions</a>
<a href="#forxmlsyntax" title="FOR XML Syntax">FOR XML Syntax</a>
</div>
</div>
<div>
<a href="composingxmlinsql.html">XML Composing Functions in SQL Statements (SQLX)</a>
</div>
<div>
<a href="xmlservices.html">Virtuoso XML Services</a>
</div>
<div>
<a href="queryingxmldata.html">Querying Stored XML Data</a>
</div>
<div>
<a href="updategrams.html">Using UpdateGrams to Modify Data</a>
</div>
<div>
<a href="xmltemplates.html">XML Templates</a>
</div>
<div>
<a href="xmlschema.html">XML DTD and XML Schemas</a>
</div>
<div>
<a href="xq.html">XQuery 1.0 Support</a>
</div>
<div>
<a href="xslttrans.html">XSLT Transformation</a>
</div>
<div>
<a href="xmltype.html">XMLType</a>
</div>
<div>
<a href="xmldom.html">Changing XML entities in DOM style</a>
</div>
<br />
</div>
<div id="text">
<a name="forxmlforsql" />
<h2>13.1. Rendering SQL Queries as XML (FOR XML Clause)</h2>
<p>
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.
</p>
<p>
The FOR XML clause has 3 variants:
</p>
<div class="formalpara">
<strong>RAW</strong>
<p>
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.
</p>
</div>
<div class="formalpara">
<strong>AUTO</strong>
<p>
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.
</p>
</div>
<div class="formalpara">
<strong>EXPLICIT</strong>
<p>
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.
</p>
</div>
<p>
In all modes, columns may either be attributes or sub-elements. The
<strong>ELEMENT</strong> keyword after the FOR XML clause forces
all columns to be rendered as sub-elements; attribute are the
default.
</p>
<p>
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.
</p>
<p>
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 <a href="fn_xml_auto.html">xml_auto()</a> function.
</p>
<div class="tip">
<div class="tiptitle">See Also:</div> <p>The <a href="htmlconductorbar.html#sqlxmlstmts">SQL-XML Statements</a> 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.</p>
</div>
<a name="forxmlexplicmode" />
<h3>13.1.1. FOR XML EXPLICIT Mode</h3>
<p>
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:
</p>
<div>
<pre class="programlisting">
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;
</pre>
</div>
<p>
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.
</p>
<p>
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.
</p>
<p>
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.
</p>
<div class="note">
<div class="notetitle">Note</div>
<p>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.
</p>
</div>
<br />
<a name="examplesofforxml" />
<h3>13.1.2. Examples of FOR XML</h3>
<p>This section gives one example of each mode of FOR XML combined
with the <span class="computeroutput">xml_auto()</span> function to help us display
the results simply. First we create a procedure that enables us to
supply SQL and return XML using the <span class="computeroutput">xml_auto()</span>
function.</p>
<div>
<pre class="programlisting">
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));
}
</pre>
</div>
<p>Now we can apply this to a couple of examples:</p>
<a name="" />
<div class="example">
<div class="exampletitle">XML RAW</div>
<div>
<pre class="programlisting">
xmla ('select "category"."CategoryID", "CategoryName",
"ProductName", "ProductID"
from "Demo".."Categories" "category", "Demo".."Products" as "product"
where "product"."CategoryID" = "category"."CategoryID" FOR XML RAW');
</pre>
</div>
<div>
<pre class="screen">
<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>
.....
</pre>
</div>
</div>
<p>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.</p>
<a name="" />
<div class="example">
<div class="exampletitle">XML AUTO</div>
<div>
<pre class="programlisting">
xmla ('select "category"."CategoryID", "CategoryName",
"ProductName", "ProductID"
from "Demo".."Categories" "category", "Demo".."Products" as "product"
where "product"."CategoryID" = "category"."CategoryID" FOR XML AUTO ELEMENT');
</pre>
</div>
<div>
<pre class="screen">
<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>
.....
</pre>
</div>
</div>
<p>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.</p>
<a name="" />
<div class="example">
<div class="exampletitle">XML EXPLICIT</div>
<div>
<pre class="programlisting">
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');
</pre>
</div>
<div>
<pre class="screen">
<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">
.....
</pre>
</div>
</div>
<p>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.</p>
<br />
<a name="forxmlfunc" />
<h3>13.1.3. Functions</h3>
<a href="fn_xml_auto.html">xml_auto()</a>
<br />
<a name="forxmlsyntax" />
<h3>13.1.4. FOR XML Syntax</h3>
<div>
<pre class="programlisting">
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
</pre>
</div>
<p>
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.
</p>
<p>
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.
</p>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="webandxml.html" title="XML Support">Previous</a>
<br />Contents of XML Support</td>
<td align="center" width="34%">
<a href="webandxml.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="composingxmlinsql.html" title="XML Composing Functions in SQL Statements (SQLX)">Next</a>
<br />XML Composing Functions in SQL Statements (SQLX)</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
763
