Re: xmltable question

--- On Wed, 8/27/08, David Wendelken <david_wendelkennc.rr.com&gt; wrote:

From: David Wendelken <david_wendelkennc.rr.com&gt;
Subject: xmltable question
To: "Multiple recipients of list ODTUG-SQLPLUS-L" <ODTUG-SQLPLUS-Lfatcity.com>
Date: Wednesday, August 27, 2008, 9:00 AM

 

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; &nbsp; 1

1.2 &nbsp;   ; &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:

 

set serveroutput on size 1000000

 

declare

 &nbsp; v_xml_folders xmltype :=

'<reports>

  <folder key="1">;

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

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

  </folder>

  <folder key="2" />

</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;   "FOLDER_KEY"&nbsp; &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; &nbsp; null

1.2 &nbsp;   ; &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.'