www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

Quick Start & Tours

Where to Start
Client Connections
Virtual Database Server
Web Server
WebDAV
Web Services
Exposing Persistent Stored Modules as Web Services
VSMX - Virtuoso Service Module for XML
SQL to XML
FOR XML Execution Modes Tables With XML Columns
NNTP
Dynamic Web Pages
VSP Examples
Third-Party Runtime Typing, Hosting & User Defined Types
Troubleshooting Tips

3.9. SQL to XML

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:

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.

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 RAW, AUTO and EXPLICIT.

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 SQL-XML Statements in the Visual Server Administration Interface section.

From Conductor XML/SQL_XML you can execute SQL query with options on how to produce XML structures from the results.

SQL to XML
Figure: 3.9.1. SQL to XML

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.

SQL to XML results
Figure: 3.9.2. SQL to XML results

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.

SQL to XML save views
Figure: 3.9.3. SQL to XML save views

You can press Edit to edit them, or Delete 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.

3.9.4. FOR XML Execution Modes

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 xml_auto() function to help us display the results simply.

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 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));
}

For more details about 'FOR XML, refer to Rendering SQL Queries as XML section of the XML Support chapter.

3.9.5. Tables With XML Columns

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 xml_tree_doc() and friends but now you can easily concentrate on what more important, the data, and not which datatype to convert it to next.

See Also:

XML Column Type