Oracle 9i R2 XML-XSLT Conversion Approach

Hello Folks,

I have an architectural design question that is based on the cabilities of the Oracle XMLTYPE datatype and functionality in the 9i Release 2 database. For the upgrade-focussed crowd out there, the company I'm working for are currently working on an 11G migration, but timescales are not going to allow this version of the database to be used on the project I'm working on.

Broadly speaking, this is what I can accomplish in 9i Release 2 at this point in time with my present knowledge:

1. Convert an XML file - stored as a CLOB in the database - into an XMLTYPE object within PL/SQL.
2. Use an XSL stylesheet in CLOB form, combined with the XMLTYPE.TRANSFORM functionality in PL/SQL, to convert the originating XML file's format into Oracle's 'Canonical' (<ROWSET>.....</ROWSET>) XML format.
3. Insert the Canonical Format XML into a standard Heap Table using the DBMS_XMLSAVE functionality.

I can make all the above work. However, the obvious configuration design would rely on storing the XSL Stylesheets in CLOB form in a database table. Whilst I'm confident this would work okay, I want to be sure I've not overlooked a better and more practical way to reference Stylesheets and perform this kind of operation at database level.

There is just so much documentation available on the 9i R2 XML functionality, that the mind boggles just trying to digest it all. There seem to be multiple and differing approaches to producing the kind of outcome I've just described. What I'm trying to investigate is if there is a "Best Practice" approach I should be considering?

If anybody can pass on any suggested approaches from their experience, or recommend a good book or reference source that isn't an Oracle Manual (I've already read those), then that would be great.

Thanks in advance for any help or suggestions.

James Received on Sat Apr 05 2008 - 04:51:30 CDT