Re: xmltype extract concatenates the results

Anurag Varma wrote:

> 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

Of course, thats much better! I knew about xmlsequence once, but forgot all about it! Received on Wed Mar 29 2006 - 13:50:08 CST