In this post I will explain how you can query an Oracle database using XSLT technology and Xalan as your XML processor.
Requirements:If you decide that you don't want to use an XML editor you will have to download
Xalan and add the necessary libraries to your classpath before processing the XSLT file. If you decide to take that route you can follow the first steps in
this tutorial, which explains how to setup your environment without an XML editor.
Tutorial1. Open Oxygen and create a
new document of type XSLT2. Copy and paste the XSLT code below:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:sql="org.apache.xalan.lib.sql.XConnection"
extension-element-prefixes="sql">
<xsl:output method="html" />
<xsl:template match="/">
<xsl:variable
name="movies"
select="sql:new('oracle.jdbc.driver.OracleDriver'
,'jdbc:oracle:thin:@
ipaddress:
sid','
username','
password')" />
<xsl:variable name="streaming" select="sql:disableStreamingMode($movies)" />
<xsl:variable
name="queryResults"
select="sql:query($movies,'
SELECT movie, actor FROM movie')" />
<html>
<head><title>Oracle Result Set</title></head>
<body style="font-family: sans-serif;">
<table border="1" cellpadding="5">
<tr>
<xsl:for-each select="$queryResults/sql/metadata/column-header">
<th><xsl:value-of select="@column-label" /></th>
</xsl:for-each>
</tr>
<xsl:apply-templates select="$queryResults/sql/row-set/row" />
</table>
</body>
</html>
<xsl:value-of select="sql:close($movies)" />
</xsl:template>
<xsl:template match="row">
<tr><xsl:apply-templates select="col" /></tr>
</xsl:template>
<xsl:template match="col">
<td><xsl:value-of select="text()" /></td>
</xsl:template>
</xsl:stylesheet>
3. Be sure to make your changes accordingly
4. After pasting your code in the text area click on the
XSLT debugger button as shown in the image below:
5. Click on the
Run button
6. Voilà! Now look at the result, and here is what I got from my XSLT:
| MOVIE | ACTOR |
|---|
| Indiana Jones and the Last Crusade | Alison Doody |
| Indiana Jones and Raiders of the Lost Ark | Harrison Ford |
| Indiana Jones and Raiders of the Lost Ark | Denholm Elliott |
| Indiana Jones and the Last Crusade | Sean Connery |
| Indiana Jones and Raiders of the Lost Ark | Jonn Rhys-Davies |
