Best approach to producing XML

[toshi]

Hello,

I have been looking at the several ways to transfrom relational data into XML data and it seems that the best route is to use SQLX functions (XMLElement, XMLAgg, etc.)

Does anybody thought otherwise and why?

Thanks

[chrisrlong]

I've actually only begun playing with it myself, and, unfortunately, only in 8i. This means that the SQL XML functions are not available to me.

Given that, I've come up thus far with the following solutions:

1. One big SQL statement with nested resultsets using the SQL CURSOR() function (note: NOT PL/SQL cursors!) Pass to DBMS_XMLQuery

2. One big SQL statement with nested resultsets using nested arrays of objects. Pass to DBMS_XMLQuery. This necessitated flattening the SQL to be no more than 2 layers deep.

3. Using PL/SQL to loop through any outer loop and pass inner-loop SQL directly to DBMSDBMS_XMLQuery.

4. Another solution might be to use XSQL. I'm waiting on the DBA to get this working, but I suspect it will perform much the same as the nested loop solution above (solution 3).

Thus far, solution 2 appears to offer the best performance, but these are only preliminary findings.

Solution 1: 9 hours
Solution 2: 2.5 hours
Solution 3: Will run tonight, but smaller tests predict ~4-6 hours

So, Solution 1 has beautifiul flexibility and is very easy to implement, but the performance sucks. Solution 2 has the best performance, but the most overhead, requiring a boatload of database type definitions (2 for each nested select - I have 10), and can only handle 2 layers (one cannot have an array with an array as an element). Solution 3 requires the most coding, but has no need for database types, so the maintenance is actually lower than solution 2. It is also more flexible than 2 and faster than 1.

The testing continues, but I'm not happy with the results so far.

Basically, I use XSLT to transform to the final results in all the solutions. The XML SQL would allow one to directly create the desired format. Note that I tried using DBMS_XMLQuery.setXSLT call to allow Oracle to do the conversion for me, but I blew out the memory. My resultset was apparently too big. My test file generates at ~550 Meg.

- Chris

[toshi]

Thanks Chris for all your insights...

Yeah I noticed that the available tools for converting relational data into XML takes a lot of time, including SQLX functions... so I'm worried that I might jump to a solution only to find out that the performance is not really good... but anyway, the options that require object type definitions is almost out of the question in my case... what I'm trying to do is some sort of an "adhoc report writer" where users can select tables and columns they want in their report... I will then dynamically create the "select" statement that will produce an XML to be passed to another program (FOP) for formatting (nice looking PDF)... so that scraps out the object type definitions because dynamically creating them would be a nightmare... that's the main reason I like SQLX functions, plus its becoming a standard accross different databases... I think I might go ahead with it...

Thanks again Chris and goodluck with what you're doing...

Any other ideas, anyone?

toshi