XML select statement to loop over all rows

Viewed 41

I have mytable consists of one XMLTYPE column called 'RS'. RS contains:

<test>
<mycol>
  <name>a</name>
   <number>1</number>
   <number>2</number>
   <number>50</number>
   <number>60</number>
</mycol>
    
<mycol>
  <name>b</name>
   <number>5</number>
   <number>820</number>
   <number>601</number>
</mycol> 

<mycol>
  <name>c</name>
   <number>6</number>
   <number>8</number>
   <number>62</number>
</mycol> 

etc...
</test>

I'm looking to run a select statement that will display ALL names and up to 2 numbers from mytable. something like this select statement but for all rows and without calling mycol[] several times.

select a.RS.extract('/test/mycol[1]/name[1]/text()').getstringval() as Names,
        a.RS.extract('/test/mycol[1]/a[1]/text()').getstringval() ||''|| 
        a.RS.extract('/test/mycol[1]/a[2]/text()').getstringval() ||''||
        a.RS.extract('/test/mycol[1]/a[3]/text()').getstringval()
        as num
from mytable a;/

output should be:

Names | num
a     | 1 2
b     | 5 820
c     | 6 8

etc...

Thanks in advance.

1 Answers

Xml_table and string-join may be helpful

Related