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 the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial