15.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.

[Tip] 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.