liorlew@gmail.com wrote:

> Hello all,
>
> I have a problem using XMLTYPE in the case that I have multiple tags
> with the same name. example:
> <tag1>
> <tag2>str1</tag2>
> <tag2>str2</tag2>
> <tag2>str3</tag2>
> <tag2>str4</tag2>
> </tag1>
>
> when I use the following query:
> select (xmltype(val)).extract('/tag1/tag2/text()).getStringVal() from
> myTable;
>
> I get the following:
> str1str2str3str4
>
> My question is, is there a method to get the data in multiple rows or
> separated by a delimiter.
>
> str1
> str2
> str3
> str4
>
> or: str1#str2#str3#str4
>
> I am using oracle 9i and the data is stored in a clob column of a
> table.

What do you need to do with the data once you get it out of the XML?

Could you do something like:

declare
xml xmltype;
v_cnt int default 0;
begin
select xmltype(clob)
into xml;

loop
if xml.existsnode('/tag1/tag2['||v_cnt||') = 0
then exit;
end if;
extract :=
xml.existsnode('/tag1/tag2['||v_cnt||'/text()).getstringval

-- process data etc

end;

Alternatively you could make the code above into a pipelined function
that will return a row each time you go around the loop.