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.

MT0 · Accepted Answer · 2022-02-04 11:16:09Z

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

answered Feb 4, 2022 at 11:16

MT0

173k12 gold badges70 silver badges136 bronze badges

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

Comments

Alex Poole · Accepted Answer · 2022-02-04 11:21:50Z

0

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

answered Feb 4, 2022 at 11:21

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

3 Comments

Jürgen Over a year ago

Thanks a lot. This helps. One more question: What i have to to, if I have the XML Declaraion in the first line of the XML Text. . Then I get the following Error: ORA-31011: XML-Parsing nicht erfolgreich ORA-19213: Fehler bei XML-Verarbeitung in Zeilen 2 LPX-00209: PI-Namen, die mit XML beginnen sind reserviert ORA-06512: in "SYS.XMLTYPE", Zeile 310 ORA-06512: in Zeile 1 31011. 00000 - "XML parsing failed" *Cause: XML parser returned an error while trying to parse the document. *Action: Check if the document to be parsed is valid.

Alex Poole Over a year ago

@Jürgen - you don't have to do anything if it is actually the first line; but if you've (for aesthetic reasons, say) added a line break before it then it will get that error; it has to really be the first line, so immediately after the opening quote. db<>fiddle

Jürgen Over a year ago

Oh yes I see. Thats exactliy what I have done. THanks

Collectives™ on Stack Overflow

Oracle XMLQuery with namespace

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related