Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Querying Oracle directly from XML

with 2 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

Advertisements

Written by maclochlainn

August 29, 2008 at 10:56 pm

Posted in Oracle, xml

Tagged with ,

2 Responses

Subscribe to comments with RSS.

  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

    September 3, 2008 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

    September 3, 2008 at 5:42 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: