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

Question

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!

Alex Poole · Accepted Answer · 2017-03-24 13:33:41Z

1

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

edited Mar 24, 2017 at 13:33

answered Mar 24, 2017 at 13:22

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MrM Over a year ago

Excellent, exactly what I was looking for!. Thanks!

Arkadiusz Łukasiewicz · Accepted Answer · 2017-03-24 13:42:27Z

0

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()' ,
 )
;

answered Mar 24, 2017 at 13:42

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related