select on multivalue xml column

User_GD3XQ

Hi all,

this is my first question on the oracle community forum, so thanks in advance for you help .

i have a database with some xml tables where there is few multivalues columns with different number set of values on each one, some times we could have just one value (which is the minimum) and some times more than one (2, 3 4 ...).

i need to select all multivalues available on this column for each id

for example if the ID XYZ contains 2 values on column 6 the query will return those two values, if its 3 ...

example of query that i am using now to extract values one by one :

select ID, extractValue(xmlrecord,'/row/c10[position()=1]') from MY_TABLE where recid = 'MY_REC_ID;

my data is stored like bellow example inside the xml column

<row id='MY_RECORD_ID'><c3>0</c3><c4>F</c4><c6>MY_DATA1</c6><c6 m='2'>MY_DATA_2</c6><c7></c7><c7 m='2'>OTHER_DATA</c7><c8>D</c8><c8 m='2'>D</c8><c9 m='2'></c9><c10 m='2'></c10><c11>OTHER_DATA3</c11><c11 m='2'></c11></row>

thank you for your help