Oracle XMLQuery with namespace

Question

I have a Oracle Table with a varchar column. The content of this column are XML Strings. Now I try to extract the value of a Tag Attribute of this column. This works fine until the XML content has a defined namespace.

The following code works fine:

with testTable(xml_val) as (select xmltype('   
    <InstanceName>          
        <PromotionInstance>       
        <Cycle> 
            <Type>
                <TypeValue days="28"/>                      
                </Type>
            </Cycle>
        </PromotionInstance>
    </InstanceName>
    ') from dual)
select xmlcast(xmlquery('/InstanceName/PromotionInstance/Cycle/Type/TypeValue/@days' passing xml_val returning content) as number(2)) as days from testTable;

But this code returns always "null":

with testTable(xml_val) as (select xmltype('   
    <InstanceName xmlns="foobar">          
        <PromotionInstance>       
        <Cycle> 
            <Type>
                <TypeValue days="28"/>                      
                </Type>
            </Cycle>
        </PromotionInstance>
    </InstanceName>
    ') from dual)
select xmlcast(xmlquery('/InstanceName/PromotionInstance/Cycle/Type/TypeValue/@days' passing xml_val returning content) as number(2)) as days from testTable;

So I've tried to find a solution. I've found out, that I have to declare the namespace in the XMLQuery. But how?

Thanks for helping me.

Answer 1

You can use XMLTABLE:

with testTable(xml_val) as (
select xmltype('   
    <InstanceName xmlns="foobar">          
        <PromotionInstance>       
        <Cycle> 
            <Type>
                <TypeValue days="28"/>                      
                </Type>
            </Cycle>
        </PromotionInstance>
    </InstanceName>
    ') from dual
)
select days
from   testTable t
       CROSS JOIN XMLTABLE(
           XMLNAMESPACES(DEFAULT 'foobar'),
           '/InstanceName/PromotionInstance/Cycle/Type/TypeValue'
           passing t.xml_val
           COLUMNS
             days NUMBER(2) PATH './@days'
       );

Which outputs:

DAYS
28

db<>fiddle here