Oracle XMLQuery with namespace-CodePudding

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.

CodePudding user response：

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

DAYS
28

db<>fiddle here

CodePudding user response：

You can declare a default namespace with:

select
  xmlcast(
    xmlquery(
      'declare default element namespace "foobar";
      /InstanceName/PromotionInstance/Cycle/Type/TypeValue/@days'
      passing xml_val
      returning content
    )
    as number(2)
  ) as days
from testTable;

Or use XMLTable:

select x.days
from testTable t
cross apply xmltable (
  xmlnamespaces(default 'foobar'),
  '/InstanceName/PromotionInstance/Cycle/Type/TypeValue'
  passing t.xml_val
  columns days number(2) path '@days'
) x;

which is more flexible, and simpler if you want to extract multiple things from the XML at once.

db<>fiddle