# 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