Connecting to Oracle from XSLT

Today I generated a report by connecting directly to an Oracle database from XSLT, and thought I'd share the basic stylesheet. I used Saxon's SQL extension, which is available when saxon8-sql.jar is on the classpath. As I was connecting to Oracle, I also needed to put ojdcb14.jar on the classpath.

Here's the stylesheet in it's most basic form, formatted for display in this blog.

The important things to note here are:

- The sql prefix is bound to "/net.sf.saxon.sql.SQLElementFactory"
- The driver is "oracle.jdbc.driver.OracleDriver"
- The connection string format is "jdbc:oracle:thin:@1.2.3.4:1234:sid" (note the colon between thin and @ - I missed that first time round) where the IP, port and sid are placeholders for the real values
- remember that saxon8-sql.jar and ojdbc14.jar needed to be on the classpath

<xsl:stylesheet version="2.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:xs="http://www.w3.org/2001/XMLSchema"
 xmlns:sql="/net.sf.saxon.sql.SQLElementFactory"
 exclude-result-prefixes="xs"
 extension-element-prefixes="sql">
 
<xsl:output indent="yes"/>

<xsl:param name="driver" 
  select="'oracle.jdbc.driver.OracleDriver'" 
  as="xs:string"/>

<xsl:param name="database" 
  select="'jdbc:oracle:thin:@123.123.123.123:1234:sid'" 
  as="xs:string"/>

<xsl:param name="user" select="'un'" as="xs:string"/>
<xsl:param name="password" select="'pw'" as="xs:string"/>

<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:variable>
 
<xsl:template match="/" name="main">
  <root>
    <sql:query connection="$connection" 
      table="some_table" 
      column="*"
      row-tag="row" 
      column-tag="col"/>
  </root>
</xsl:template>

</xsl:stylesheet>

The result of this transform outputs XML in the form:

<root>
  <row>
    <col>data1</col>
    <col>data2</col>
    <col>data3</col>
    <col>data4</col>
  </row>
  ....
</root>

where <root> is the wrapper element, and <row> and <col> are the element names specified in the <sql:query> element.

And that's it - connecting to an Oracle database from within XSLT.