RE: xmltable question

Had two typos when I copied it. <oops />

 

Here it is again:

 

 

I have a simple xml structure.

 

<reports>

  <folder key="1">

 &nbsp; &nbsp; &nbsp; <folder key=";1.1" />

 &nbsp; &nbsp; &nbsp; <folder key=";1.2" />

  </folder>

  <folder key="2" />

</reports>

 

I want to query this back and return a table with two columns:

 

folder_key  ;  folder_parent_key

1 &nbsp;   &nbsp; &nbsp; &nbsp; &nbsp;   ; null

1.1   ; &nbsp; &nbsp; &nbsp; &nbsp;   1

1.2   ; &nbsp; &nbsp; &nbsp; &nbsp;   1

2 &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp; null

 

I can't get it to tell me the parent folder's key value.

 

Here's my code:

 

declare

 &nbsp; v_xml_folders xmltype := xmltype.createxml(

'<reports>

  <folderroot>

 &nbsp;  <folder key=";1">;

 &nbsp; &nbsp; &nbsp; <folder key="1.1" />

 &nbsp; &nbsp; &nbsp; <folder key="1.2" />

 &nbsp;  </folder>

  <folder key="2" />

  </folderroot>

</reports>');

cursor folder_list is

select folders.*

 &nbsp; from  XMLTable

 &nbsp; &nbsp; &nbsp;  ('/reports/*//folder'

 &nbsp; &nbsp; &nbsp; &nbsp;  passing v_xml_folders

 &nbsp; &nbsp; &nbsp; &nbsp;  columns

 &nbsp; &nbsp; &nbsp; &nbsp;   &quot;FOLDER_KEY"&nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; VARCHAR2(100) PATH '/folder/key'

 &nbsp; &nbsp; &nbsp; &nbsp;  ,"FOLDER_PARENT_KEY" VARCHAR2(100) PATH '/parent::node()/key'

 &nbsp; &nbsp; &nbsp;  ) folders;

BEGIN

 &nbsp; for folder_rec in folder_list

 &nbsp; loop

 &nbsp; &nbsp; &nbsp; DBMS_OUTPUT.PUT_LINE('key=' || folder_rec.folder_key);

 &nbsp; &nbsp; &nbsp; DBMS_OUTPUT.PUT_LINE('par=' || folder_rec.folder_parent_key);

 &nbsp; end loop;

END;

/

 

folder_key  ;  folder_parent_key

1 &nbsp;   &nbsp; &nbsp; &nbsp; &nbsp;   ; null

1.1   ; &nbsp; &nbsp; &nbsp; &nbsp;   null

1.2   ; &nbsp; &nbsp; &nbsp; &nbsp;   null

2 &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp; null

 

I've tried PATH '../key' and a host of other variations.

 

Anyone know how to do this? ; Forums are down, and the oracle docs are terrible.'