Databases

Steve Muench

| I have a need to store many xsl/xml documents in a 
| database and would like to know what the best solution
| everyone has found is.  I am using Oracle 8i on Solaris,
| and Java.

Assuming you don't *want* to break down the templates
into their own tables (for perhaps doing some dynamic
assembly of the right set of templates based on
runtime personalization info), just storing entire
XML documents as is with Oracle8i your best options are:

   -> CLOB  (Character Large OBject)
   -> BFILE (Reference to an external file)

Both of these types support streaming input and output
interfaces in JDBC. The big difference is that the CLOB
is updateable and stored *in* the database and can be
XML-Search indexed (might not be something you want
to do on Stylesheets, but...) while the BFILE is a 
read-only reference to a file on the external filesystem.

You can read a CLOB from a table like...

  public static CLOB read(Connection conn,
                          String tableName,
                          String colName,
                            String idCol,
                               int idVal) throws Exception {

    PreparedStatement p = 
         conn.prepareStatement("SELECT " + colName   +
             "  FROM " + tableName +
             " WHERE " + idCol     + "= ?");
    p.setInt(1,idVal);
    OracleResultSet rs = (OracleResultSet)p.executeQuery();
    rs.next();
    CLOB theClob = rs.getCLOB(1);
    rs.close();
    p.close();

    return theClob;
  }

And parse an XML document from a clob like:

  public static XMLDocument read( CLOB theClob ) throws Exception {

    // Create an oracle.xml.parser.v2.DOMParser 
    DOMParser theParser = new DOMParser();

    // Get the input stream from the CLOB
    Reader in = theClob.getCharacterStream();

    // Parse the document from the stream
    theParser.parse(in);
    in.close();

    // Get the parsed XML Document from the parser
    return theParser.getDocument();

  }


Mike Kay adds:

In my own experiments some time ago I found that the most
efficient serial format for a document was - guess what -
XML. I found it was significantly quicker to reparse the XML
than to "unserialize" the serialized DOM, largely because
the XML is far smaller.

I've used the technique of breaking up the XML document into
logical units (e.g. a single entry in a dictionary) and
storing each as an XML fragment in a VARCHAR field, having
copied any attributes that are significant for query
purposes into separate columns of the table.

Another technique you don't mention is to store the DOM as
persistent objects in an object database. I haven't had the
chance to compare that. It should work well in theory.

Paul Tchistopolskii / Steve Muench

I suggest to take a look at http://www.pault.com/Pxsl/
PXSLServlet v 0.2. is a wrapper around XT and it allows
feeding XT with the data from SQL server as if it is XML.

and from Steve M


If you get your hands on the free XSQL Servlet from Oracle,
it makes doing what you're doing very easy against Oracle
and non-Oracle databases running under any servlet engine.

You type in your query, you provide a stylesheet.
Presto.

Live demos running at:

http://technet.oracle.com/tech/xml/demo/demo1.htm

and the demos are all included in the release to learn from.

Download by visiting:

http://technet.oracle.com/tech/xml/xsql_servlet

and clicking on the "Software" icon at the top.

and from Mike Kay


You might like to look at the SQL extension elements in
Saxon: a very simple "demo" facility, but with a little
energy it could be turned into something very useful for
loading XML data into relational databases.

> Another question is if XSLT can take other
> datasource other than XML for exporting data from database. I 
> am exploring the solutions, so any thoughts are appreciated.

Yes. Most XSLT processors will accept input from any SAX
parser, so all you need is to write an implementation of the
SAX parser interface to supply the data.

Steve Muench

The Oracle XSQL Servlet is geared to help you do
exactly this. Questions on it should be asked in
our Online Tech support forum where we'll be happy
to help you figure out what you're trying to do.

http://technet.oracle.com/tech/xml

Q: Saxon:while
A: Mike Kay

> e.g., speaking in a Perl-esque way, I want
>   while ($foo != "bar") {
>     blah
>   }
> 
> Is there any way to simulate, hack, or otherwise achieve this?

Pure XSLT is side-effect free, so the value of $foo cannot
change within the loop, therefore this construct would be of
limited value. Saxon does provide the extension element
saxon:while, as has been mentioned, but it is only useful in
conjunction with extension functions or extension elements
(such as saxon:assign) that have side-effects. An example:

<div xmlns:tok="java.util.StringTokenizer">
<xsl:variable name="tokens" select="tok:StringTokenizer.new('a bag of
worms')"/>
<saxon:while test="tok:hasMoreTokens()">
   <word><xsl:value-of select="tok:nextToken($tokens)"></word>
</saxon:while>
</div>

Here nextToken() is an extension function that has a
side-effect, namely moving the current position.

Unlike xsl:for-each, saxon:while is strictly sequential. As
David Carlisle mentioned, you can't predict the order of
execution of xsl:for-each. I don't know of an XSLT processor
that executes xsl:for-each non-sequentially, but it's
certainly permitted: hence the absence of an <xsl:break>
instruction.

Paul Tchistopolskii

This site Should answser all your questions!

Chris Bayes adds

You could look at my site if you just want it in a file.

Steve Muench

The Oracle "XSQL Pages" publishing framework is one of the easiest ways to do this. It simplifies combining SQL, XML, and XSLT with simple-to-author templates.

Templates can be processed in command-line and Servlet mode, as well as be used directly from your own java code.

It's part of the "XDK for Java".