Re: xmltype extract concatenates the results

liorlew_at_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. Received on Sun Mar 26 2006 - 13:10:43 CST