# 13. XML Support

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
XML Support
Rendering SQL Queries as XML (FOR XML Clause)
XML Composing Functions in SQL Statements (SQLX)
Virtuoso XML Services
Querying Stored XML Data
Using UpdateGrams to Modify Data
XML Templates
Syntax Saving SQL Queries to XML Template Saving XQUERY Queries to XML Template Saving XPATH Queries to XML Template Programmatic Examples
XML DTD and XML Schemas
XQuery 1.0 Support
XSLT Transformation
XMLType
Changing XML entities in DOM style

## 13.6. XML Templates

Virtuoso XML templates allow execution of queries over HTTP in order to achieve any combination of the following:

- Execution of SQL queries returned in an XML formatted resultset.
- Execution of XQuery and XPath queries against XML and SQL-XML documents in the Virtuoso WebDAV repository.
- Use an XML notation (Updategrams) to insert/update/delete SQL data.

An XML template is an XML file containing a query, optional parameters with default values for the query, a place to specify an XSL stylesheet, and a section for specifying updatagram based syncronization metrics. They are meant to be executed as an XML described short-cut to an query result, an XML document. The XML document returned from calling an XML template can be served raw, or transformed using XSLT.

XML templates can be executed from within Virtuoso procedure language using the xml_template() function, or published as SOAP compliant XML Web Services, which makes them accessible to any SOAP and WSDL aware environment.

XML templates provide quick easy access to results from a SQL query as usual, but now this can be saved to a file. The results are not saved, just the query definition. You can use this feature to rapidly produce dynamic reports that can potentially be rendered in different ways by providing an alternate stylesheet. The report can be refined on the fly by providing parameters for the query. The output is reachable via HTTP directly by providing the URL to the template.

When XML templates are executed via HTTP, the XSLT transformation will be performed automatically if the "sql:xsl" attribute is specified in the templates root element. This means that the default XSLT transformation cannot be avoided if the template is executed over HTTP. However, you have the option whether to apply the transformation or not when using the xml_template() function. XML templates containing <sql:query> elements return either results or messages regarding the queries. An XSLT transformation can be made on the result from xml_template() using the returned XSLT sheet URL. Hence it is conditional in PL programming. The application developers can choose to either use the style sheet specified in the template, use another style sheet, or skip XSLT transformation entirely. This feature provides full flexibility for the procedure developer.

XML templates are intended for execution over HTTP. The template file can be stored either on the file system, WebDAV repository or stored on another HTTP server being referenced by a URL parameter. Specifying a template as a URL parameter naturally introduces a new potential security risk associated with the template owners web server.

Figure: 13.6.1. Conceptual View of XML Templates

Queries and updategrams can be parameterised. The parameters must be defined in the header element, which consists of zero or more param elements. The default value of the parameter is enclosed within the param element whose values are typically replaced during the execution phases. When XML templates are executed from the xml_template() function the parameters are specified in a vector as an input parameter. When XML templates are executed via HTTP the parameters are contained in the URL. Important: All <sql:param> parameter definitions in the template for SQL queries need to be named parameters, and the names must be preceded by a colon i.e. :ParamName (note colon at the front). The colon is not required for updategrams.

If an error occurs as a result of executing a SQL query or updategram, the comment will be included in the result detailing the error. The subsequent queries and updategrams will still be executed.

See Also:

The Exposing Persistent Stored Modules as Web Services section as this also describes how XML Templates can be published to web services as a SOAP messages using a PL Wrapper. This is achieved using the Administrative Interface.

xml_template()

### 13.6.2. Syntax

The format of an XML template is shown below, illustrating how SQL queries and updategrams are specified:

    <root xmlns:sql="urn:schemas-openlink-com:xml-sql" sql:xsl='xslt url' >
      <sql:header>
        <sql:param name=":...">..</sql:param>
        <sql:param name=":...">..</sql:param>...
      </sql:header>
      <sql:query>
        select ... for xml ..
      </sql:query>
      <sql:sync>
        Update gramm
      </sql:sync>
    </root>

The root element can contain two attributes. The first is the required namespace declaration of xmlns:sql="urn:schemas-openlink-com:xml-sql", required to identify the XML as an XML template. The second attribute allows the specification of an optional XSLT stylesheet to be applied to the resulting XML document, if applicable. The XSLT stylesheet file can be specified as either a relative or absolute URL.

The following elements can be defined in the "sql" namespace for an XML Template:

- The "root" tag can be any name, its purpose is to provide the top-level element required for any well-formed XML, and provides the root element for the resulting XML document, if any.
- <sql:header> This tag is used to hold the parameters for templates execution such as <sql:param> elements. The <sql:header> element allow us to define multiple parameters.
- <sql:param> This element is used to define a named parameter that is passed to the query and/or updategram inside the template. Each <xsl:param> element defines one parameter. Multiple <xsl:param> elements can be specified within the <sql:header> element.
- <sql:query> This element is used to specify SQL/XML queries. It is possible to specify multiple <sql:query> elements within a single template.
- <sql:sync> This element is used to contain updategrams.

<sql:query> and <sql:sync> entries are executed sequentially in the order they appear as separate transactions. Therefore, if you have multiple <sql:query> or <sql:sync> elements in the template, if one fails, the others will proceed.

XML templates can be executed directly from Virtuoso PL using the xml_template() function.

To allow XML template execution from a Virtuoso virtual directory and it's descendants the special option: 'xml_templates' must be used in the virtual directory definition. This option can be set from the Virtual Directories user interface or from SQL (or ISQL) using the vhost_define(.... , opts=>vector('xml_templates', 'yes')); function. In the usual way, the SQL user specified for VSP execution within the virtual directory definition will be used for executing the templates within such web directories. If your XML Templates are to run from a DAV directory then you must allow suitable execute permissions for the file and directory.

When a virtual directory is configured to allow execution of XML templates be aware that this also means that descendant directories inherit this property. Also be aware that this also allows execution of VSP files in the normal way. WebDAV also has another layer of security attributes that will need to be set to enable files to be executable. By default files in DAV do not have execute privileges.

Explicitly specifying "contenttype=text/html" as a URL parameter will instruct the Virtuoso HTTP server to return the HTML output.

See Also

Functions: xml_template(), xmlsql_update(), xml_auto().

UpdateGrams.

The FOR XML clause.

### 13.6.3. Saving SQL Queries to XML Template

Using the Visual Server Administration Interface, we are going to make and save a query to an XML template in WebDav, and then demonstration its use from a browser.

First we will make a new WebDAV directory. From the Menu choose WebDAV/Content Management:

Figure: 13.6.3.1. Creating a new directory (collection) in WebDAV

Next we need to configure a virtual directory mapping to this so that XML Templates can be executed later:

Figure: 13.6.3.2. Configuring a Virtual Directory to respond to XML Template requests from our Dav

Now we go to the Interactive SQL interface and use a simple query. Here we are assuming that the Demo database is being used, so we will query the Customers table:

Figure: 13.6.3.3. A SQL Query with results

Next we will press the Save button to go to the Save XML Template page. Here we can supply some "FOR XML" clause, "FOR XML AUTO" being the simplest. We must save the template in the directory we created earlier, and as the user DAV. The filename we have selected is /DAV/xmlt/sql-template.xml.

Figure: 13.6.3.4. The Save XML Template page and settings

Once the template has been saved we can test it. Point your browser at you newly created file, a URL similar to: http://myserver/xmlt/sql-template.xml:

Figure: 13.6.3.5. The test results: http://myserver/xmlt/sql-template.xml

See Also:

Exposing XML Templates as Web Services.

### 13.6.4. Saving XQUERY Queries to XML Template

Using the Visual Server Administration Interface, we are going to make and save a query to an XML template in WebDav, and then demonstration its use from a browser.

First we will make a new WebDAV directory. From the Menu choose WebDAV/Content Management:

Figure: 13.6.4.1. Creating a new directory (collection) in WebDAV

Next we need to configure a virtual directory mapping to this so that XML Templates can be executed later:

Figure: 13.6.4.2. Configuring a Virtual Directory to respond to XML Template requests from our Dav

Now we turn to the XQUERY tool, from the Visual Server Administration Interface menu choose Query Tools/XQUERY.

We will start by testing the following query against the Demo database

    <bib>
       {
       for $b in document("bib.xml")/bib/book
       where $b/publisher = "Addison-Wesley" and $b/@year > 1991
       return
          <book year = {$b/@year}>
             {$b/title}
          </book>
       }
    </bib>

Figure: 13.6.4.3. XQUERY query against the Demo database

Now we will save this as an XML template by pressing the Save button. The query we used will be copied to the save XML template page. We will ensure that the name of the file is /DAV/xmlt/xquery-template.xml and that the DAV owner is set to DAV:

Figure: 13.6.4.4. Saving the XML template for the Xquery query

See Also:

Exposing XML Templates as Web Services.

### 13.6.5. Saving XPATH Queries to XML Template

Using the Visual Server Administration Interface, we are going to make and save a query to an XML template in WebDav, and then demonstration its use from a browser.

First we will make a new WebDAV directory. From the Menu choose WebDAV/Content Management:

Figure: 13.6.5.1. Creating a new directory (collection) in WebDAV

Next we need to configure a virtual directory mapping to this so that XML Templates can be executed later:

Figure: 13.6.5.2. Configuring a Virtual Directory to respond to XML Template requests from our Dav

Now we set our sights on the XML files in WebDAV. We will perform a very simple XPATH query on an XML source file from this documentation. We will search for the existence of <title> tags in /DAV/docsrc/adminui.xml:

Figure: 13.6.5.3. XPATH query on a file in WebDAV

Make a quick mental note of the trivial result. Now we will save our query as an XML template to be executed over HTTP. Pressing the Save button takes us to the save XML Template page. We ensure that the file to save is set to /DAV/xmlt/xpath-template.xml and the file owner is set to DAV:

Figure: 13.6.5.4.

Once our query has been saved in an XML template we can test it by pointing a web browser at the URL for the file as we configured our virtual directory for, which will be similar to: http://myvirtuoso:8890/xmlt/xpath-template.xml:

Figure: 13.6.5.5.

See Also:

Exposing XML Templates as Web Services.

### 13.6.6. Programmatic Examples

Executing an XML template from the file system

First step is to setup the /xmlt directory on file system. The function vhost_define() will be used to enable execution of XML templates. DBA privileges are required to use this function:

    SQL> vhost_define (lpath=>'/xmlt', ppath=>'/xmlt/',
      vsp_user=>'demo', opts=>vector('xml_templates', 'yes'));

Now the following files must be stored as: <www-root>/xmlt/file1.xml

    <?xml version ='1.0' encoding='UTF-8'?>
     <root xmlns:sql='urn:schemas-openlink-com:xml-sql' sql:xsl='shippers.xsl'>
       <!-- XML template example -->
       <!-- parameters declaration -->
        <sql:header>
          <sql:param name=":ShipperID">2</sql:param>
          <sql:param name=":CompanyName">United Package (you should see me)</sql:param>
          <sql:param name=":Phone">(503) 555-3199</sql:param>
        </sql:header>
       <!-- XML updategram , this will update the second record -->
        <sql:sync>
            <sql:before>
                <Shippers sql:id="1" ShipperID=":ShipperID"/>
            </sql:before>
            <sql:after>
                <Shippers sql:id="1" ShipperID=":ShipperID" CompanyName=":CompanyName" Phone=":Phone"/>
            </sql:after>
        </sql:sync>
       <!-- make a parametrized query -->
       <sql:query>
          SELECT ShipperID, CompanyName,Phone FROM Shippers where ShipperID = :ShipperID FOR XML AUTO
       </sql:query>
       <!-- make an error to see what happens -->
       <sql:query>
          select * from NotExist for xml auto
       </sql:query>
    </root>

Now we want an XSL stylesheet stored as: <www-root>/xmlt/shippers.xslt containing the following:

    <?xml version="1.0"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
        <xsl:output method="html" indent="yes" />

        <xsl:template match="/">
    	<html>
    	    <head>
    	    <title>Shippers list</title>
    	    </head>
    	    <body>
    	    <xsl:apply-templates/>
    	    </body>
    	</html>
        </xsl:template>

        <xsl:template match="root">
    	<table>
    	    <tr><td>ID</td><td>Name</td><td>Phone</td></tr>
    	    <xsl:apply-templates/>
    	</table>
        </xsl:template>

        <xsl:template match="Shippers">
    	<tr>
    	    <td><xsl:value-of select="@ShipperID"/></td>
    	    <td><xsl:value-of select="@CompanyName"/></td>
    	    <td><xsl:value-of select="@Phone"/></td>
    	</tr>
        </xsl:template>
    </xsl:stylesheet>

Point your web browser to:

    http://[host:port]/xmlt/file1.xml

and then at:

    http://[host:port]/xmlt/file1.xml?:ShipperID=3

for the results.

Executing an XML template from WebDAV

The first step is to create a new DAV collection and configure it to allow XML template execution. DBA privileges are required for these operations. To create a DAV collection the function dav_col_create() can be used, followed by the vhost_define() function to allow XML template execution:

    SQL> dav_col_create ('/DAV/xmlt/', '110100100', 'dav', 'dav', 'dav', 'dav');

    SQL> vhost_define (lpath=>'/DAV/xmlt', ppath=>'/DAV/xmlt/',
      is_dav=>1, vsp_user=>'demo', opts=>vector('xml_templates', 'yes'));

Now, the file can be uploaded. The same file as in the previous example will be used, copied from the file system location to the DAV location: <www-root>/DAV/xmlt/file1.xml

    SQL> dav_res_upload ('/DAV/xmlt/file1.xml',
      file_to_string (concat (http_root(), '/xmlt/file1.xml')), 'text/xml',
      '111101101N', 'dav', 'dav', 'dav', 'dav');

Likewise the XSLT stylesheet from the previous example will also be used in the same way, placing into the DAV location: <www-root>/DAV/xmlt/shippers.xslt

    SQL> dav_res_upload ('/DAV/xmlt/shippers.xsl',
      file_to_string (concat (http_root(), '/xmlt/shippers.xsl')),
      'text/xsl', '110100100N', 'dav', 'dav', 'dav', 'dav');

This example can now be demonstrated by trying the following URLs in your web browser:

    http://[host:port]/DAV/xmlt/file1.xml

and

    http://[host:port]/DAV/xmlt/file1.xml?:ShipperID=3

Previous Using UpdateGrams to Modify Data | Chapter Contents | Next XML DTD and XML Schemas