Re: [xsl] XSLt in RDBMs engine? (was XML to Database Inserts via XSLT)

[Barry Lay <blay@xxxxxxxxxxxxxxxxxxx>]

Brian Chrisman wrote:

> I *think* I understand what you are talking about here...
> I was considering the idea of caching data locally... but
> then I realized that in order to have access to all the 
> data I might actually need, I'd have to cache about the
> entire database.. :-)  I could mark out some subsections
> to cache, but then I'd have to go back to SQL and break
> out of XSLt to get the data I need on a miss.... which
> is just as much work code-wise...
>  
A common solution for this is "lazy loading": grab what you need in 
blocks when you need it as opposed to what you "might" need.  I don't 
know if there are any XML representations that let you actually 
accomplish this during the XSL operation - it seems most transformers 
want the entire XML source parsed before starting the transformation.

> Does Oracle have an XSLt engine embedded in the database
> engine itself?  When it comes down to it, that's the tool
> I'm really looking for... :-)
>  
Oracle has a full JVM built right in which allows you to write stored 
procedures in Java, for example.  You can also perform XML operations in 
PL/SQL (Oracle's native stored procedure language).  It is my 
understanding that Oracle has or had plans to rewrite some of the Java 
code in C to speed up the XML processing; I don't know how it went.  I 
don't believe that you can just hand an XSL stylesheet to the database 
processor but you could fetch the database data into XML in a stored 
procedure and apply a stylesheet there before returning it to your 
application.  If I understand your question it gives you the ability to 
accomplish what you are after.

> I know postgres allows you to store/query XML data *within*
> a data field in the database... but I'm looking to actually
> treat the database itself like an XML document.... and have
> the stylesheet evaluate xpath expressions inside the database
> engine, thereby avoiding having to copy data out of the
> database and then throw 99.9% of it away before the presentation 
> layer.
>  
Oracle lets you use XPath statements in your SQL.  You can save your XML 
either as a big string as you describe or in an object-relational 
structure that puts the complex types in your XML schema into separate 
tables.  You can also mix the two approaches.  Doing this will allow you 
to use fairly fancy queries to generate XML right out of the database.  
By default it will use the the object views to name the elements and 
attributes in the resulting XML but you do have some control over this.

I will have to caution you, though.  The object views that enable this 
are built on top of Oracle "TYPEs", which are essentially object 
definitions for the database.  These definitions are not database schema 
specific.  For those not familiar with the terminology database schemas 
offer a similar function to namespaces - they specify a qualifier for a 
named entity.  Since Oracle TYPEs are not schema specific if you create 
a TYPE called "CUSTOMER" which corresponds to a complex type "customer" 
in an XML schema, every user of the database now refers to your 
definition of "CUSTOMER" when it is referenced.  Also, by default, all 
named things in Oracle are folded to uppercase which may look ugly to 
those used to lower case or camel case in XML.  You can get around this 
by quoting things in the definitions but many database tools (like Toad) 
really don't like this.

> I've really done a lot of looking around for this kind of
> solution before... posting here is just kind of a last-gasp
> attempt on my part.... I'm designing a rewrite of a decent
> sized software project, and if something like this exists, 
> I want to use it.. :-)
>
> thanks for your input!
>
> -Brian
I don't think that there is an off-the-shelf solution that will let you 
just pretend that an Oracle database is XML.  Databases like Oracle are 
designed to be pretty robust and one of the ways they achieve that is to 
prevent structural changes that will destabilise things.  If you follow 
a similar controlled approach to your XML and have an XML schema 
definition for your data then you can process the XSD using a stylesheet 
to create the Oracle objects that will make the database produce 
conformant XML.  I have had a reasonable amount of success with this 
approach.  In fact I had more success getting the technique to work than 
getting my more traditionally-minded colleagues to accept it.  Amusingly 
enough it was only the DBA (usually the most conservative person in a 
development group) that showed real interest in a tool that could easily 
crank out massive amounts of DDL in response to a design change.

Barry