The Saxon SQL Extension

The Saxon distribution includes a set of extension elements providing access to SQL databases. These are not intended as being necessarily a production-quality piece of software (there are many limitations in the design), but more as an illustration of how extension elements can be used to enhance the capability of the processor.

Contents

Specification
Example

To use the SQL extension elements in a stylesheet, you need to define a namespace prefix (for example "sql") in the extension-element-prefixes attribute of the xsl:stylesheet element, and to map this prefix to namespace URI that ends in "/net.sf.saxon.sql.SQLElementFactory".

Specification

This extension defines five new stylesheet elements: sql:connect, sql:query, sql:insert, sql:column, and sql:close, described in the following sections.

sql:connect

sql:connect creates a database connection. It has attributes driver, database, user, and password, all of which are attribute value templates (so the values can be passed in as parameters). The driver attribute names the JDBC driver class to be used. The database name must be a name that JDBC can associate with an actual database.

The sql:connect instruction returns a database connection as a value, specifically a value of type "external object", which can be referred to using the type java:java.sql.Connection. Typically the value will be assigned to a variable using the construct:


<xsl:variable name="connection" 
                 as="java:java.sql.Connection" 
                 xmlns:java="http://saxon.sf.net/java-type">
  <sql:connect database="jdbc:odbc:testdb" 
       driver="sun.jdbc.odbc.JdbcOdbcDriver" 
       xsl:extension-element-prefixes="sql"/>
</xsl:variable>

This can be a global variable or a local variable; if local, it can be passed to other templates as a parameter in the normal way. The connection is used on instructions such as sql:insert and sql:query with an attribute such as connection="$connection"; the value of the connection attribute is an expression that returns a database connection object.

sql:query

sql:query performs a query, and writes the results of the query to the result tree, using elements to represent the rows and columns. If you want to process the results in the stylesheet, you can write the results to a temporary tree by using the sql:query instruction as a child of xsl:variable. The attributes are as follows:

connection	The database connection. This is mandatory, the value is an expression, which must evaluate to a database connection object as returned by `sql:connect`.
table	The table to be queried (the contents of the FROM clause of the select statement). This is mandatory, the value is an attribute value template.
column	The columns to be retrieved (the contents of the SELECT clause of the select statement). May be "*" to retrieve all columns. This is mandatory, the value is an attribute value template.
where	The conditions to be applied (the contents of the WHERE clause of the select statement). This is optional, if present the value is an attribute value template.
row-tag	The element name to be used to contain each row. Must be a simple name (no colon allowed). Default is "row".
column-tag	The element name to be used to contain each column. Must be a simple name (no colon allowed). Default is "col".
disable-output-escaping	Allowed values are "yes" or "no", default is "no". The value "yes" causes the content of all rows/columns to be output as is, without converting special characters such as "<" to "<". This is useful where the database contains XML or HTML markup that you want to be copied into the result document. Use this option with care, however, since it applies to all the columns retrieved, not only to those that contain XML or HTML. An alternative is to use the saxon:parse() extension function to process the contents of an XML column.

sql:insert and sql:column

sql:insert performs an SQL INSERT statement. This causes a row to be added to the table identified by the "table" attribute. There is a mandatory connection attribute, used as in the sql:query instruction described above.

sql:column is used as a child element of sql:insert, and identifies the name and value of a column to be included in the INSERT statement. The name of the column is identified by the name attribute, the value may be indicated either by evaluating the expression contained in the select attribute, or as the expanded contents of the sql:column element. The value is always interpreted as a String. (Remember this is purely a demonstration of extensibility, in a real system there would be a need to cater for SQL columns of other data types).

sql:close

sql:close closes the database connection. There is a mandatory connection attribute, used as in the sql:query instruction described above.

Example

A specimen stylesheet that uses these XSL extension is books-sql.xsl. This loads the contents of the books.xml file into a database table, To use it, you need to create a database database containing a table "Book" with three character columns, "Title", "Author", and "Category"

Here is the stylesheet:


<xsl:stylesheet
	xmlns:sql="java:/net.sf.saxon.sql.SQLElementFactory"
 	xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
	xmlns:saxon="http://saxon.sf.net/"
 	extension-element-prefixes="saxon sql">

<!-- insert your database details here, or supply them in parameters -->
<xsl:param name="driver" select="'sun.jdbc.odbc.JdbcOdbcDriver'"/>
<xsl:param name="database" select="'jdbc:odbc:test'"/>  
<xsl:param name="user"/>
<xsl:param name="password"/>

<!-- This stylesheet writes the book list to a SQL database -->

<xsl:variable name="count" select="0" saxon:assignable="yes"/>

<xsl:output method="xml" indent="yes"/>

<xsl:template match="BOOKLIST">
    <xsl:if test="not(element-available('sql:connect'))">
        <xsl:message>sql:connect is not available</xsl:message>
    </xsl:if>
    
    <xsl:message>Connecting to <xsl:value-of select="$database"/>...</xsl:message>

    <xsl:variable name="connection" 
         as="java:java.lang.Object" xmlns:java="http://saxon.sf.net/java-type">   
      <sql:connect driver="{$driver}" database="{$database}" 
                 user="{$user}" password="{$password}">
        <xsl:fallback>
          <xsl:message terminate="yes">SQL extensions are not installed</xsl:message>
        </xsl:fallback>
      </sql:connect>
    </xsl:variable>
    
    <xsl:message>Connected...</xsl:message>
    
    <xsl:apply-templates select="BOOKS">
      <xsl:with-param name="connection" select="$connection"/>
    </xsl:apply-templates>
    
    <xsl:message>Inserted  records.</xsl:message>

    <xsl:variable name="book-table">
      <sql:query connection="$connection" table="book" column="*" row-tag="book" column-tag="col"/> 
    </xsl:variable>
    
    <xsl:message>There are now <xsl:value-of select="count($book-table//book)"/> books.</xsl:message>
    <new-book-table>
        <xsl:copy-of select="$book-table"/>
    </new-book-table>
    
    <sql:close connection="$connection"/>
    
</xsl:template>

<xsl:template match="BOOKS">
    <xsl:with-param name="connection"/>
    <xsl:for-each select="ITEM">
    	<sql:insert connection="$connection" table="book">
    	    <sql:column name="title" select="TITLE"/>
            <sql:column name="author" select="AUTHOR"/>
            <sql:column name="category" select="@CAT"/>
    	</sql:insert>
    	<saxon:assign name="count" select="$count+1"/>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

To run this stylesheet you will need to do the following:

Create a database (e.g. Microsoft Access) containing a table "Book" with three character columns, "Title", "Author", and "Category".
Register this database as a JDBC data source. (If you use Microsoft Access, register it as an ODBC data source called, say, Books, and then it will automatically be available under JDBC as "jdbc:odbc:Books".
Modify the <sql:connect> element in the stylesheet to specify the correct JDBC connection name for the database, and if necessary to supply a username and password. Alternatively you can supply the driver class, database name, username, and password as parameters on the command line.
Execute the stylesheet from the command line, as follows:

java net.sf.saxon.Transform data\books.xml books-sql.xsl

The database will be populated with data from the books.xml document.

A Warning about Side-Effects

XSLT does not guarantee the order of execution of instructions. In principle, the sql:close instruction could be evaluated before the sql:query instruction, which would obviously be disastrous.

In practice, Saxon's order of execution for XSLT instructions is reasonably predictable unless you use variables, or unless you invoke stylesheet functions from within an XPath expression. Using the SQL extension instructions within templates that are directly invoked is ths fairly safe, but it is not a good idea to invoke them as a side-effect of computing a variable or invoking a function. The exceptions are sql:connect and sql:query: if you use these instructions to evaluate the content of a variable, then of course they will be executed before any instruction that uses the value of the variable.

Michael H. Kay
5 November 2003