MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Querying Oracle directly from XML

with 7 comments

I’d gone through Doug Tidwell’s XSLT (2nd Edition), I was disappointed that there wasn’t any coverage in Chapter 9 about how to connect through an XSLT Processor to an Oracle database. Overall, the book is improved. I’d recommend it as a reference or a detailed read for the lost truth of XML. Seriously, it’s a very readable book.

I didn’t find anything through a quick search of the web, and I figured a little example might be nice. I used Xalan-Java of the example.  Xalan-Java is a convenient open source XSLT Processor, covered in an earlier blog. You can refer to that blog page for the download location and general configuration of Xalan-Java. There is one more configuration step after those in the other blog entry. You need to put the Oracle JDBC driver file in the class path. That’s where the Oracle class files are located. This example uses ojdbc5.jar from an Oracle Database 11g home on Windows:

C:\>SET CLASSPATH=%CLASSPATH%;C:\oracle\11.1.0\db_1\jdbc\lib\ojdbc5.jar

You can write this sample file, substituting a user, password, table and column (or two) from your own database:

The hidden rows didn’t fit nicely in the other image, but they’re required to make it work. There in the next image. They are the templates for handling columns and rows.

The command line generates an HTML page for you and redirects it to a file:

C:\> java org.apache.xalan.xslt.Process -XSL oracle_xml_query.xsl > sample.htm

or, the preferred syntax:

C:\> java org.apache.xalan.xslt.Process -XSL oracle_xml_query.xsl -OUT sample.htm

As per the request, here’s the native code for an XML direct query against an Oracle database.

<?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:@mclaughlin11g:1521:orcl','plsql','plsql')" />
<xsl:variable name="streaming" select="sql:disableStreamingMode($movies)" />
<xsl:variable
  name="queryResults"
  select="sql:query($movies,'SELECT item_title, item_subtitle FROM item')" />
<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>

Written by maclochlainn

August 29th, 2008 at 10:56 pm

Posted in Oracle,xml

Tagged with ,

7 Responses to 'Querying Oracle directly from XML'

Subscribe to comments with RSS or TrackBack to 'Querying Oracle directly from XML'.

  1. Nice tip. I’m not a big fan of the way you paste in code. 1) not searchable by Google 2) not copy and pasteable 3) difficult to read. Maybe 1 and 2 are intended, but it would make it easier to implement your ideas if they were just changed to display in a different font.

    Mike Farmer

    3 Sep 08 at 4:23 am

  2. It was a struggle to get the benefit of overlay but lose the ability to copy the code. Unfortunately, the code gets ordinary apostrophes converted to specialized characters that make copying fail without editing. I use images and provide the code at the bottom of static pages but not for blog entries. My intent is to add more content here and move it all to a static page. When I do that, I’ll put the source code at the bottom. Hope that works for you? If you have a better alternative, please let me know.

    maclochlainn

    3 Sep 08 at 5:42 pm

  3. Can you please post sources code, please?

    John

    9 Feb 09 at 11:08 pm

  4. Mike & John,

    I’ve updated the post with the original code. Thanks for asking … :-)

    maclochlainn

    17 Feb 09 at 12:19 am

  5. Great article. I’ve been digging all day and finally came across this. However, I’m trying to use Excel as my source of data, and I cannot ‘save it to xml’ or use a .csv as my input as is the case with pretty much every other thing I’ve found online as a solution.

    I’m now struggling to figure out the correct connection string in the “sql:new(…” variable to get it to open my odbc datasource.

    You wouldn’t happen to know how to do that would you? I tried the following:

    “newelements” is the name of my system data source which points to the excel spreadsheet.

    I’m just learning this stuff and am using xalan-j 2.7.1 and tweaking their xml to html sample to build my knowledge of the various features of xslt processing.

    Any help you could give would be appreciated.

    – FM

    Patrick James

    12 Mar 10 at 6:56 pm

  6. I haven’t used this Open Source library yet, it’s on the list but not probably as soon as you’ll need it.

    maclochlainn

    12 Mar 10 at 11:11 pm

  7. […] shows the event driven XML approach against MySQL database […]

Leave a Reply