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.

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:

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.

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.

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>
    
    <sql:connect driver="{$driver}" database="{$database}" 
                 user="{$user}" password="{$password}">
        <xsl:fallback>
          <xsl:message terminate="yes">SQL extensions are not installed
        </xsl:fallback>
    </sql:connect>
    
    <xsl:message>Connected...</xsl:message>
    
    <xsl:apply-templates select="BOOKS"/>
    
    <xsl:message>Inserted  records.</xsl:message>

    <xsl:variable name="book-table">
    <sql:query 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/>
    
</xsl:template>

<xsl:template match="BOOKS">
    <xsl:for-each select="ITEM">
    	<sql:insert 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.

Michael H. Kay
12 November 2002