xpath - How to query XML with namespaces in Oracle?

Question

I need to extract data in PLSQL procedure from XMLType variable containing complete XML document, with following structure (below simplified):

<?xml version="1.0" encoding="utf-8"?>
<AA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://my.domain/cat1/">
  <Element>
    <ID>2</ID>
    <Value>46544</Value>
  <Element>
</AA>

I'm using XMLTable function, but with simple /AA/Element XPath expression getting no data:

SELECT C1, C2
INTO v_id, v_val
FROM XMLTable('/AA/Element'
                   passing v_MyXML columns
                    C1 number path 'ID',
                    C2 number path 'Value'
                )

Neither with any of below expressions:

'/*.AA/Element'
'declare default element namespace "http://my.domain/cat1/"; /AA/Element'
'declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance"; declare namespace xsd="http://www.w3.org/2001/XMLSchema"; declare default element namespace "http://jpk.mf.gov.pl/wzor/2016/03/09/03094/"; /AA/Element'

Only way I was able to extract the data was to modify document/variable and simply replace

<AA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://my.domain/cat1/">

with

<AA>

It's not the perfect solution as I need to modify the document and return it's initial structure with proper attributes. Could anybody suggest how to modify XPath expression to be able to fetch data? Or maybe use any other method ignoring namespaces from AA element?

Answer 1

/*.AA/Element

You need to separate namespace and element name by a colon :, not a dot .. Furthermore, in this case the namespace is inherited to the <Element/> child, so a proper query would be

/*:AA/*:Element

Your query declaring a default element namespace should be working fine. I could not find a trace that Oracle wouldn't support this.

declare default element namespace "http://my.domain/cat1/";
/AA/Element

In your last example, you defined the other namespaces.

declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
declare namespace xsd="http://www.w3.org/2001/XMLSchema";
declare default element namespace "http://jpk.mf.gov.pl/wzor/2016/03/09/03094/";
/AA/Element

I guess the my.domain namespace is actually this one. In the end, this is the same query as in example 2: the other two namespace prefixes are newer used. If the wildcard namespace still does not work out, try to register the namespace with a prefix (choose a proper prefix, which you can define on your own):

declare namespace cat1="http://my.domain/cat1/";
/cat1:AA/cat1:Element