hello i have xml say like this for example
<row>
<c1>a</c1>
<c2 >b</c2>
<c3>c</c3>
<c4>d</c4>
<c4 @m=2>d2</c4>
<c5>e</c5>
<c6>f</c6>
</row>
here's my Query
SELECT XT.*
FROM table1 X,
XMLTABLE ('/row/*'
PASSING X.XMLRECORD
COLUMNS MV NUMBER PATH 'data(/./@m)',
SV NUMBER PATH 'data(/./@s)',
TAG_NAME VARCHAR2 (100) PATH 'name(/.)',
VALUES_ VARCHAR2 (1000) PATH '.') XT
so my target table should be like this
MV SV TAG_NAME VALUES_
null null c1 a
null null c2 b
null null c3 c
null null c4 d
2 null c4 d2
null null c5 e
null null c6 f
what i want to do is to take only tags c4,c5,c6
i mean to filter it before parsing the data not after
i tried using loops like this
SELECT XT.*
FROM table1 X,
XMLTABLE (for $x in'/row/*'
where 'name(/.)' in ('c4','c5','c6')
PASSING X.XML
COLUMNS MV NUMBER PATH 'data(/./@m)',
SV NUMBER PATH 'data(/./@s)',
TAG_NAME VARCHAR2 (100) PATH 'name(/.)',
VALUES_ VARCHAR2 (1000) PATH '.') XT
but it took a lot of time due to massive number of data i also tried this
SELECT XT.*
FROM table1 X,
XMLTABLE ('/row/c4|/row/c5|/row/c6'
PASSING X.XML
COLUMNS MV NUMBER PATH 'data(/./@m)',
SV NUMBER PATH 'data(/./@s)',
TAG_NAME VARCHAR2 (100) PATH 'name(/.)',
VALUES_ VARCHAR2 (1000) PATH '.') XT
but same problem it took a lot of time also bec massive number of data if there's a way to filter before parsing without using 2 solutions i just showed you