Storing database-query-results in a "table-variable"

orgler

Hi,

I'm writing a BIRT report for the following scenario:
I've got a lot of 'diagrams', where each diagram is stored in a XMLTYPE Column in my database. The XML-Structure for each diagram consists of lots of elements (called activities and objectstores), and for each activity/object store, there are some URLs in the XML-File.

From this data, I created the following report using multiple lists:
First I have a List for all diagrams. In the detail of this list, I nested a second list for every activity and after this list for every objectstore. In these two list (among other things), there is a third list nested printing out the urls.

This is fine from the functional view. But it gives me (on database-level), lots of parsing: Each XML-Diagram gets parsed once for the first list, once for the nested list and finally (and this is really ugly) for each activity and each objectstore in it for the third list. This gives me a total of 1+1+object_store_per_diag+activities_per_diag xml-parsings of the same xml-file for each diagram.

It would be great if I could bring this down to 3 (at least). So this is what I thought of: In the first list, I'd like to fetch all documents for all activities in the current diagram into a "list-variable". In the third list-level, instead of fetching the data from the database, I'd then look up my data in this list-variable (filtered for the actual objectstore/activity). This would reduce my parsing-effort on database-level from 1+1+5+15=22 to 3 which would be a great improvement.

For some supported databases, there is a feature called xmlindex which speeds up the xml-parsing on the database-level. But since some of our users don't have that (quite expensive db-edition), I'd like to improve my report at least in the described way.

Can I, and if so: how can I, achieve this in BIRT?

I don't like to join all my data on all diagrams, activities/objectstores and urls into one big view and then group on this table in BIRT :-(


Any help would be highly appreciated,

-orgler