SQL extension instructions example

A specimen stylesheet that uses these Saxon SQL extension instructions is books-sql.xsl. This loads the contents of the books.xml file into a database table. (These sample resources are found in the saxon-resources download file.) To use it, you need to create a database containing a table "Book" with three character columns, "Title", "Author", and "Category" (see below).

Here is the stylesheet:

<xsl:stylesheet xmlns:sql="http://saxon.sf.net/sql" 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>

Running the example using Microsoft Access

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

  1. Create a database (e.g. Microsoft Access) containing a table "Book" with three character columns, "Title", "Author", and "Category".

  2. 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".)

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

  4. Execute the stylesheet from the command line, as follows:

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

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

Running the example using MySQL

The following instructions illustrates how to run the stylesheet using MySQL, under a UNIX platform:

  1. Create the database, using MySQL.

  2. Register the database as a JDBC data source (same as 2. above). However, change the xsl:param elements with attribute names "driver" and "database" as follows (we assume machine is localhost):

    <xsl:param name="driver" select="'com.mysql.jdbc.Driver'"/> <xsl:param name="database" select="'jdbc:mysql://localhost:3306/Book'"/>

  3. Download the MySQL connector jar file, if missing.

  4. Execute the stylesheet from the command line (same as 4. above). The kind of problems that might occur are as follows:

    • "JDBC Connection Failure: com.mysql.jdbc.Driver": Ensure the MySQL connector jar file is downloaded and in the classpath.

    • "SQL extensions are not installed": The binding of the namespace for the SQL extension must be present in the Configuration object and must match the URI in the stylesheet. For execution of the stylesheet from the command line, the following is required:

      java   net.sf.saxon.Transform   -config:data/config.xml   data/books.xml   style/books-mysql.xsl

      where the configuration file data/config.xml includes the following entry:

      <configuration xmlns="http://saxon.sf.net/ns/configuration" edition="EE"> <xslt> <extensionElement namespace="http://saxon.sf.net/sql" factory="net.sf.saxon.option.sql.SQLElementFactory"/> </xslt> </configuration>