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.

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.

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

liorlew@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,

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

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!