Querying XMLType data (CLOB storage)

From: Ralph Weires <weiresr_at_fh-trier.de>
Date: Wed, 03 Sep 2003 16:16:14 +0200
Message-ID: <bj4teb$3v9$1@news.uni-kl.de>

Hi all,

i got a problem querying data stored as XMLType (CLOB) in Oracle 9iR2.

My table XMLTABLE consists of the two rows

   ID (INTEGER)
   DATA (SYS.XMLTYPE) Assume i stored the following two example documents in two rows of the table:

ROW 1:
   ID: 1

   DATA: <document>


           <person>

             <name>Someone</name>


           </person>

         </document>

ROW 2:
   ID: 2

   DATA: <document>


           <person>

             <name>Anyone</name>


           <person>

           <person>

             <name>Someone else</name>


           </person>

         </document>

When i afterwards perform a query like the following:

   SELECT x.DATA.extract('/document/person/name/text()').getStringVal() NAMES
   FROM XMLTABLE x;

the result is:

   NAMES

---

   Someone
   AnyoneSomeone else

The result i'd like to get i something like

   NAMES

---

   Someone
   Anyone
   Someone else

with the different matching entries of a *single* row being separated in multiple rows of the returned result set.

I know that if i omit the text()-function in the XPath-Statement, I would get the different entries ('Anyone' and 'Someone else') separated by 'name'-tags, but i don't want to handle XML data in my (Java) application - that's what i want the DB to do.

I've been searching a long time for a solution, but unfortunately all examples i found for querying XML data in Oracle did not include a problem like this...

Thanks for any advice,
Ralph Weires Received on Wed Sep 03 2003 - 09:16:14 CDT