XQuery How to filter the elements in xml before parsing data without using loops or pipeline

51 views Asked by At

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

0

There are 0 answers