CODEWITHSUNDEEP
xmlxpathoracle11g
MrM
MrM

Reputation: 399

Oracle 11g xml - return row where child node does not exist

I have a table holding an xmlstructure 

create table xml_stg_test (rawdata xmltype);

insert into xml_stg_test (rawdata) values (xmltype.createxml('<root>
    <tmp1>
        <val1>123</val1>
    </tmp1>
    <tmp1>
        <val1>234</val1>
        <tmp2>
            <val2>567</val2>
        </tmp2>
    </tmp1>
</root>'));


Select extractvalue(value(rec), '*/val1') test
from xml_stg_test sg, table(xmlsequence(extract(rawdata,'*/tmp1'))) rec;

**TEST**
1 123
2 234

I wish to only return the node which does not have child node <tmp2> (i.e row 1). Is this possible to achieve via query? Perhaps by use of member function existnode?

Thanks!

Upvotes: 0

Views: 943

Answers (2)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

If i understand your question correctly. You want to return only leaf node. 

select * from xmltable('//*[not(*)]' passing xmltype('<root>
    <tmp1>
        <val1>123</val1>
    </tmp1>
    <tmp1>
        <val1>234</val1>
        <tmp2>
            <val2>567</val2>
        </tmp2>
    </tmp1>
</root>'  )
columns 
 leaf_name varchar2(100) path 'name()', 
 leaf_value varchar2(100) path 'text()' ,
 )
;

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191560

You could get both the node value you're interested in, and the child node (if it exists) via an XMLTable call instead, and then filter those rows which don't have the child:

select xt.test
from xml_stg_test xsg
cross join xmltable (
  '/root/tmp1'
  passing xsg.rawdata
  columns test number path 'val1',
    filter xmltype path 'tmp2'
) xt
where xt.filter is null;

      TEST
----------
       123

I've left the data type of the filter column as XMLType but you could get the actual value form the node if you know the type. This would catch empty child nodes too though.

Or you can filter directly in the XPath:

select xt.test
from xml_stg_test xsg
cross join xmltable (
  'for $n in /root/tmp1 where not(exists($n/tmp2)) return $n'
  passing xsg.rawdata
  columns test number path 'val1',
    filter xmltype path 'tmp2'
) xt;

      TEST
----------
       123

Upvotes: 1

Related Questions