Search code examples
sqlxmloraclexml-parsingxmltable

How to use Oracle XMLTable to retrieve the value/condition inside a tag?

I have a SQL using XMLTable to retrieve tag values such as Tag>Value</Tag which works fine. But what about value/condition inside the tag? See following XML with tag T2 having a remove condition = true. How can I retrieve that condition using XMLTable?

<ns:T1>
    <ns:T2 remove="true"></ns:T2>
<ns:T1>
Solution

  • Pretty much the same way, just use the XPath syntax to match attributes. Leaving out namespaces for the illustration below (to keep it simple):

    select *
from xmltable(
    '/'
    passing xmltype('<T1><T2 remove="true"></T2></T1>')
    columns remove varchar2(6) path '/T1/T2/@remove')
;

REMOVE
------
true

    Note that in Oracle SQL there are no boolean values, so you can only retrieve this as a string, not a Boolean. If you need it in a condition, you can't say where REMOVE; you will need to write where REMOVE = 'true'