<!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="3. Quick Start & Tours" />
<meta name="dc.subject" content="3. Quick Start & Tours" />
<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="quicktours.html" title="Chapter Contents" />
<link rel="prev" href="qsvsmx.html" title="VSMX - Virtuoso Service Module for XML" />
<link rel="next" href="qsnntp.html" title="NNTP" />
<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>3. Quick Start & Tours</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="qssqltoxml" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>3. Quick Start & Tours</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="quicktours.html">Chapter Contents</a> | <a class="link" href="qsvsmx.html" title="VSMX - Virtuoso Service Module for XML">Prev</a> | <a class="link" href="qsnntp.html" title="NNTP">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="quicktours.html">Quick Start & Tours</a>
</div>
<br />
<div>
<a href="newadminui.html">Where to Start</a>
</div>
<div>
<a href="qsclientcon.html">Client Connections</a>
</div>
<div>
<a href="qsvdbsrv.html">Virtual Database Server</a>
</div>
<div>
<a href="qswebserver.html">Web Server</a>
</div>
<div>
<a href="qswebdav.html">WebDAV</a>
</div>
<div>
<a href="qswebservices.html">Web Services</a>
</div>
<div>
<a href="qstexpwsmodules.html">Exposing Persistent Stored Modules as Web Services</a>
</div>
<div>
<a href="qsvsmx.html">VSMX - Virtuoso Service Module for XML</a>
</div>
<div class="selected">
<a href="qssqltoxml.html">SQL to XML</a>
<div>
<a href="#qsforxmlmodes" title="FOR XML Execution Modes">FOR XML Execution Modes</a>
<a href="#qsxmlcolumn" title="Tables With XML Columns">Tables With XML Columns</a>
</div>
</div>
<div>
<a href="qsnntp.html">NNTP</a>
</div>
<div>
<a href="vspquickstart.html">Dynamic Web Pages</a>
</div>
<div>
<a href="qsvspexamples.html">VSP Examples</a>
</div>
<div>
<a href="qshostingplugs.html">Third-Party Runtime Typing, Hosting & User Defined Types</a>
</div>
<div>
<a href="troutips.html">Troubleshooting Tips</a>
</div>
<br />
</div>
<div id="text">
<a name="qssqltoxml" />
<h2>3.9. SQL to XML</h2>
<p>Virtuoso enables you to develop eBusiness solutions that use XML as both a Data Source
and Data Interchange format. Your XML Data documents can take the form of Pure XML Documents,
or documents that are transformed from SQL-XML on the fly. By supporting the XPATH query
language for XML Data, you are able to use an industry standard query language to query entire XML
Documents or portions of XML Documents stored within Virtuoso. Virtuoso's inclusion of an
XSLT transformation engine then allow you to transform XML data for other needs.
These XML documents are openly accessible to user agents such as Web Browsers via HTTP
and/or WebDAV. These XML documents are described as being dynamic because they have varying
degrees of sensitivity to changes that occur in the underlying database tables from which the
XML data originates. Virtuoso allows you to create two types of XML documents from homogeneous
or heterogeneous SQL Data on the fly:
</p>
<ul>
<li>
<strong>Transient</strong> The materialization of the XML Document occurs at
the time of file opening, this implies that data from the original SQL database(s) is
retrieved and then transformed into XML in one operation. This format of SQL-XML document is
highly sensitive to source database(s) changes.
</li>
<li>
<strong>Persistent (Time Synchronized)</strong> - The materialization of the
XML Document re-occurs at a user configurable interval after initial creation. This is a
caching scheme which is less sensitive to changes in the source databases(s) in favor of performance.
</li>
</ul>
<p>SQL-XML documents may be Valid or Well Formed XML documents, this includes support for both
DTDs and XML Schemas which my be external entity references or inlined within the XML Documents prologue
in the case of DTDs.
</p>
<p>Virtuoso supports an extended SQL syntax that is identical to that implemented by Microsoft
SQL Server for the purpose of creating SQL-XML documents. These SQL extensions take the form of a
new "FOR XML" clause that includes three main options which control the structure of the resulting
XML document tree. These options are <strong>RAW</strong>, <strong>AUTO</strong> and
<strong>EXPLICIT</strong>.
</p>
<p>Virtuoso's HTML based graphical interface includes a user friendly mechanism for
creating dynamic XML documents from SQL data using the "FOR XML" extended SQL syntax.
The dynamic XML documents created by this process are typically stored in Virtuoso's
WebDAV repository. Documents stored in this repository are accessible by any XML
consuming client application via HTTP, Windows Web Folders, or any other WebDAV or
HTTP compliant environment. A description of the interface in general can be found in
the <a href="htmlconductorbar.html#sqlxmlstmts">SQL-XML Statements</a> in the
<a href="">Visual Server Administration Interface</a> section.
</p>
<p>From Conductor <strong>XML/SQL_XML</strong> you can execute SQL query with options
on how to produce XML structures from the results.
</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="SQL to XML" src="../images/ui/qssql2xml001.png" />
</td>
</tr>
<tr>
<td>Figure: 3.9.1. SQL to XML</td>
</tr>
</table>
<p>The illustration above depicts the fact that only minor changes to standard SQL are
required in order to create powerful dynamic XML documents from SQL. It also illustrates
how the entire process of controlling the type and format of the XML documents and their
actually WebDAV storage is all achieved without any programming. The XML document extract
below is a depiction of the XML document tree produced using the "FOR XML" AUTO option.
</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="SQL to XML results" src="../images/ui/qssql2xml002.png" />
</td>
</tr>
<tr>
<td>Figure: 3.9.2. SQL to XML results</td>
</tr>
</table>
<p>The Virtuoso Demo database provides a set of sample tables in the Demo catalogue,
and some sample XML views that use them. The "StoredQueries" tab lists saved XML Views as shown below. </p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="SQL to XML save views" src="../images/ui/qssql2xml003.png" />
</td>
</tr>
<tr>
<td>Figure: 3.9.3. SQL to XML save views</td>
</tr>
</table>
<p>You can press <strong>Edit</strong> to edit them, or <strong>Delete</strong>
to remove them or click on the XML FILE itself to see the results
in your default browser, a sample of the output is shown above.</p>
<a name="qsforxmlmodes" />
<h3>3.9.4. FOR XML Execution Modes</h3>
<p>Now we will consider the programmatical approach along side the visual interface
approach. We will have one example of each of the modes of FOR XML combined with
the <a href="fn_xml_auto.html">xml_auto()</a> function to
help us display the results simply.</p>
<p>For the programmatical examples to run smoothly using ISQL a number of steps are
required to obtain textual output from the xml_auto() function which usually is expected
to output directly to an HTTP target. To make the demonstration simpler a utility procedure
will be created that will simply enable us to supply SQL and return XML using
the <a href="fn_xml_auto.html">xml_auto()</a> 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>
<ul>
<li>
<p>
<strong>RAW</strong> mode produces an XML entity from
each row of the result set, and does 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>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="SQL to XML using FOR XML RAW mode" src="../images/ui/qssql2xml103.png" />
</td>
</tr>
<tr>
<td>Figure: 3.9.4.1. SQL to XML using FOR XML RAW mode</td>
</tr>
</table>
<p>The same SQL statement containing the FOR XML syntax is used in the
visual interface shown above, and in the programmatical version shown below.
This is because both use the xml_auto() function for generating results.
In the visual interface once the settings and query have been confirmed
you press the "Execute" button to store the query in the specified DAV location.</p>
<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 class="note">
<div class="notetitle">Note:</div>
<p>The xmla function is not a standard function but quick
utility for quickly rendering a text output for
the <a href="fn_xml_auto.html">xml_auto()</a> function. The definition is
at the top of this section</p>
</div>
<p>The resulting XML from either ISQL or the saved links on the
visual interface will yield:</p>
<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 Fantastica" 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>
.....
</pre>
</div>
</li>
<li>
<p>
<strong>AUTO</strong> mode. 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>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="SQL to XML using FOR XML AUTO mode" src="../images/ui/qssql2xml102.png" />
</td>
</tr>
<tr>
<td>Figure: 3.9.4.1. SQL to XML using FOR XML AUTO mode</td>
</tr>
</table>
<p>The same SQL statement containing the FOR XML syntax is used in the
visual interface shown above, and in the programmatical version shown below.
This is because both use the xml_auto() function for generating results.
In the visual interface once the settings and query have been confirmed
you press the execute button to store the query in the specified DAV location.</p>
<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');
</pre>
</div>
<div class="note">
<div class="notetitle">Note:</div>
<p>The xmla function is not a standard function but quick
utility for quickly rendering a text output for
the <a href="fn_xml_auto.html">xml_auto()</a> function. The definition is
at the top of this section</p>
</div>
<p>The resulting XML from either ISQL or the saved links on the
visual interface will yield:</p>
<div>
<pre class="screen">
<category CategoryID="1" CategoryName="Beverages">
<product ProductName="Chai" ProductID="1">
</product>
<product ProductName="Chang" ProductID="2">
</product>
<product ProductName="Guaran Fantsstica" ProductID="24">
</product>
<product ProductName="Sasquatch Ale" ProductID="34">
</product>
<product ProductName="Steeleye Stout" ProductID="35">
</product>
<product ProductName="C(te de Blaye" ProductID="38">
</product>
<product ProductName="Chartreuse verte" ProductID="39">
</product>
<product ProductName="Ipoh Coffee" ProductID="43">
</product>
<product ProductName="Laughing Lumberjack Lager" ProductID="67">
</product>
.....
</pre>
</div>
<div class="note">
<div class="notetitle">Note:</div>
<p>In contrast to the RAW mode AUTO produces results that are more reasonable and intuitive.
Only one category element is used for each category which contains all the children of that
category.</p>
</div>
</li>
<li>
<p>
<strong>EXPLICIT</strong> 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 2 first columns, TAG and
PARENT.</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="SQL to XML using FOR XML EXPLICIT mode" src="../images/ui/qssql2xml101.png" />
</td>
</tr>
<tr>
<td>Figure: 3.9.4.1. SQL to XML using FOR XML EXPLICIT mode</td>
</tr>
</table>
<p>The same SQL statement containing the FOR XML syntax is used in the
visual interface shown above, and in the programmatical version shown below.
This is because both use the xml_auto() function for generating results.
In the visual interface once the settings and query have been confirmed
you press the execute button to store the query in the specified DAV location.</p>
<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 class="note">
<div class="notetitle">Note:</div>
<p>The xmla function is not a standard function but quick
utility for quickly rendering a text output for
the <a href="fn_xml_auto.html">xml_auto()</a> function.
The definition is at the top of this section</p>
</div>
<p>The resulting XML from either ISQL or the saved links on the
visual interface will yield:</p>
<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&#225; Fant&#225;stica</NAME></PRODUCT>
<PRODUCT PID="34">
<NAME>Sasquatch Ale</NAME></PRODUCT>
<PRODUCT PID="35">
<NAME>Steeleye Stout</NAME></PRODUCT>
<PRODUCT PID="38">
<NAME>C&#244;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&#246;nbr&#228;u Klosterbier</NAME></PRODUCT>
<PRODUCT PID="76">
<NAME>Lakkalik&#246;&#246;ri</NAME></PRODUCT>
</CATEGORY>
<CATEGORY CID="2" NAME="Condiments">
<PRODUCT PID="3">
.....
</pre>
</div>
<div class="note">
<div class="notetitle">Note:</div>
<p>In contrast again, the EXPLICIT mode produces exactly what we asked for.</p>
</div>
</li>
</ul>
<br />
<p>For more details about 'FOR XML, refer to <a href="forxmlforsql.html">
Rendering SQL Queries as XML</a> section of the XML Support chapter.</p>
<a name="qsxmlcolumn" />
<h3>3.9.5. Tables With XML Columns</h3>
<p>XML is a new native Virtuoso datatype, based on an extension of LONG
VARCHAR for compatibility with ODBC clients, allows direct storage,
retrieval and querying of XML stored in a database table. This has always been
possible with Virtuoso utilizing <span class="computeroutput">xml_tree_doc()</span> and friends
but now you can easily concentrate on what more important, the data, and not
which datatype to convert it to next.</p>
<div class="tip">
<div class="tiptitle">See Also:</div>
<p>
<a href="sqlrefxmldatatype.html">XML Column Type</a>
</p>
</div>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="qsvsmx.html" title="VSMX - Virtuoso Service Module for XML">Previous</a>
<br />VSMX - Virtuoso Service Module for XML</td>
<td align="center" width="34%">
<a href="quicktours.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="qsnntp.html" title="NNTP">Next</a>
<br />NNTP</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
>
832
