Re: xmltype extract concatenates the results

From: Anurag Varma <avoracle_at_gmail.com>
Date: 27 Mar 2006 13:39:58 -0800
Message-ID: <1143495598.166842.266800@i39g2000cwa.googlegroups.com>

I'm assuming you have defined this column as clob .. and not as xmltype.
Try this for example:

ORA92> desc txml
 Name
Null? Type

---

• -------------------------------------------- A NUMBER B CLOB

ORA92> select * from txml;

         A B

---

---

         1  <tag1>
                <tag2>str1a</tag2>
                <tag2>str2a</tag2></tag1>

         2  <tag1>


               <tag2>str1</tag2>

               <tag2>str2</tag2>

               <tag2>str3</tag2>

               <tag2>str4</tag2>

           </tag1>

ORA92> col val format a10
ORA92> l
  1 select a, extract(value(t1),'/tag2/text()').getStringVal() val   2* from txml t, TABLE( xmlsequence (extract (xmltype(t.b), '/tag1/tag2' ) ) ) t1
ORA92> /          A VAL
---------- ----------

         1 str1a
         1 str2a
         2 str1
         2 str2
         2 str3
         2 str4

6 rows selected.

Anurag Received on Mon Mar 27 2006 - 15:39:58 CST