Re: xmltype extract concatenates the results

liorlew_at_gmail.com wrote:

> I need to populate an array (proC). I guess , if it works that it will
> be fine.
> I will try to check it.
>
> thanks
> Lior

I had a go a trying something out:

select
xmltab.xml.extract('/tag/tag1['||rnum||']/text()').getstringval() from
  ( select rownum rnum,
xmltype.createxml('<tag><tag1>x</tag1><tag1>y</tag1></tag>') xml

    from dual
    connect by level <= 10) xmltab
where xmltab.xml.existsnode ('/tag/tag1['||rnum||']/text()') = 1;

This 'works' but I am not sure how it performs. If you know what the total number of 'tag1's you can encounter in your XML this might work for you, but you would need to test if it performs reasonably.

Ie change the level <= 10 line to whatever the highest expected occurrance of your repeating tag is.

The only other way I can think of to achive what you want is to use a pipelined function - try both and see which performs best. I suspect if the total recurring tags is low, the above query will perform better, but I haven't tested it. If you can have any number of repeating tags, then a pipelined function would be the way to go.

Thats not to say there is not a better way that I have not thought of - If you come up with one post back here, as I would be interested in it!

Cheers, Received on Mon Mar 27 2006 - 14:58:14 CST