CODEWITHSUNDEEP
xmloracle-databaseplsql
fawick
fawick

Reputation: 590

Generate XML from Oracle PL/SQL multilevel collection

I am quite amateur on Oracle DB and PL/SQL in general, but need to interface with a Oracle DB nevertheless.

I have a PL/SQL Stored Procedure that has an output parameter which is a multilevel collection:

declare
TYPE level_a_type IS RECORD
(
    text varchar2(6),
    data number
);
TYPE level_a_table IS TABLE of level_a_type INDEX BY PLS_INTEGER;
TYPE level_b_type IS RECORD
(
    value number,
    included_table level_a_table
);
TYPE level_b_table IS TABLE of level_b_type INDEX BY PLS_INTEGER;

I call the procedure and generate XML manually on stdout like this 

set serveroutput on
declare
    result level_b_type;
    n integer;
begin
    n := mypackage.mystoredprocedure(result);
FOR i IN 1 .. result.count LOOP
    dbms_output.putline('<levelb value="'||result(i).level_b_value||'" >');
    FOR k IN 1 .. result(i).included_table.count LOOP
        dbms_output.putline('<levela text="'||result(i).included_table(k).text||'" data="'||result(i).included_table(k).data||" '/>');
    END LOOP;
    dbms_output.putline('</levelb>');
END LOOP;
end;

However, this is quite cumbersome to maintain. So, is there a PL/SQL shortcut for do something like XML_gen(result) that will retrospect into level_b_table and generate something like this automatically?

<levelb value="1">
<levela text="Hello" data="1" />
<levela text="World" data="2" />
</levelb>

Upvotes: 0

Views: 2440

Answers (2)

Jim Hudson
Jim Hudson

Reputation: 8069

What I do in cases like this is create an object-relational view that contains the data you need. That is,

create view level_b_v of level_b_table with object identifier (value) as
select <level b stuff>,
    cast (multiset (select <level a stuff>) as level_a_table;

Then I use dbms_xmlgen to select the multilevel XML from the view (pretty much just "select * from level_b_v".

And, if necessary, use dbms_xslprocessor to apply a stylesheet. That's a DOM operation which uses a lot of memory (and takes resources as the 4th power of row size) but you can have DBMS_XMLGEN select data in chunks and process them if your transforms are pretty localized.

You need a unique object identifier for the view. And you need to create the types, but you've already done that.

There are other ways, of course, but this has worked very well for us for moderate-sized data, up to about 10 levels in the heirarchy and 400 MB of resulting XML.

Upvotes: 2

Szilard Barany
Szilard Barany

Reputation: 1135

Not much of a help, I know, but have a look at the Oracle XML DB documentation. Oracle provide a lot of functionality to create/manipulate XML documents.

Upvotes: 0

Related Questions