SAXONICA

Saxon Documentation
Full Contents
About Saxon
Changes in this Release
Conditions of Use
Using XSLT 2.0
Using XQuery
Handling Source Documents
XML Schema Processing
The XPath API
Standards Conformance
Extensibility
Saxon Extensions
Sample Saxon Applications
The Saxon SQL Extension
| Introduction
| sql:connect
| sql:query
| sql:insert and sql:column
| sql:close
» | Example
| A Warning about Side-Effects
XSLT Elements
XPath 2.0 Expression Syntax
Function Library
XSLT Patterns

# 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.sql.Connection" 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 <xsl:value-of select="$count"/> 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: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.

Next