Accessing a Database in Saxon

The Saxon XSLT processor provides a set of extension elements that provide SQL functions. There are extension elements to connect and disconnect from a database, to run a database query, and to do updates, inserts, and deletes on database tables. To keep our example simple, we’ll use <sql:connection> element to connect to a database, then we’ll use <sql:query> to select items from the database and <sql:close> to close the connection.

Here’s the complete stylesheet:

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

  <xsl:output method="html"/>

  <xsl:template match="/">

    <!-- Create the JDBC connection -->
    <xsl:variable name="connection"
      as="java:java.sql.Connection" 
      xmlns:java="http://saxon.sf.net/java-type">
      <sql:connect database="jdbc:derby://localhost:1527/books" 
        driver="org.apache.derby.jdbc.ClientDriver"
        user="doug"
        password="x"/>
    </xsl:variable>

    <!-- Run the query -->
    <xsl:variable name="queryResults">
      <sql:query connection="$connection” table="compbks"
        column="*" row-tag="tr" column-tag="td"/>
    </xsl:variable> <html> <head> <title>Computer books in our database</title> </head> <body style="font-family: sans-serif;"> <h1>Computer books in our database</h1> <p>Here are the <xsl:value-of select="count($queryResults/tr)"/> computer books we ...

Get XSLT, 2nd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.