SQL & PL/SQL

Hi,

I'm on 11.2.0.3.

I have the following table:

create global temporary table tmp_pc_compare_result

(

    table_name varchar2(30),

    table_key varchar2(100),

    parent_table varchar2(30),

    parent_table_key varchar2(100),

    diffxml xmltype,

    has_diff number(1)

) on commit delete rows;

Let's assume the following data:

I need to generate an XML which looks as follows

<ENTITIES>

     <ENTITY>

          <TABLE NAME=table_name>

               <DIFF>...</DIFF>

               <TABLE NAME=child table name - recursively

                    diffxml

                    ...  

                </TABLE>

          </TABLE>

     </ENTITY>

</ENTITIES>

1. The XML must be ordered by the parent table's key (table_key) - recursively.

2. every row with HAS_DIFF=1 must include the entire path up to root parent, even if the parent(s) have HAS_DIFF=0. no need to include any child rows below the last HAS_DIFF=1.

so the XML should look like:

<ENTITIES>

     <ENTITY>

          <TABLE NAME="TABLE1">

               <DIFF>20</DIFF>

               <TABLE NAME="CHILD1">

                    <DIFF>40</DIFF>

                </TABLE>

               <DIFF>30</DIFF>

          </TABLE>

     </ENTITY>

</ENTITIES>

I can do it with PL/SQL in terms of cursors and loops.

I was wondering if it can be done by SQL only.

I started experimenting with some hierarchical queries (both connect by and subquery factoring) but I wasn't able to get the order of rows quite right.

"start with HAS_DIFF=1" and going up will return all the rows I need, but I cannot seem to order them correctly for the XML:

select TABLE_NAME, TABLE_KEY, PARENT_TABLE, PARENT_TABLE_KEY, x.DIFFXML.getclobval(), HAS_DIFF,level lvl

from tmp_pc_compare_result x

start with has_diff=1

connect by table_name=prior parent_table and table_key=prior parent_table_key;

with "start with PARENT_TABLE is null" I cannot seem to filter only the rows I need (rows which has  HAS_DIFF=1 somewhere in their path).

Any ideas would be welcome (I may just be wanting too much from the SQL engine though )

Thanks,

Mor