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?

Solution 1

@JensErat already provided the XML background so I don't have to. Instead below you'll find a working example how to apply all that in Oracle PL/SQL.

You need to use XML namespace clause of xmltable:

The XMLNAMESPACES clause contains a set of XML namespace declarations. These declarations are referenced by the XQuery expression (the evaluated XQuery_string), which computes the row, and by the XPath expression in the PATH clause of XML_table_column, which computes the columns for the entire XMLTable function. If you want to use qualified names in the PATH expressions of the COLUMNS clause, then you need to specify the XMLNAMESPACES clause.

You can also use default XML namespace clause:

xmlnamespaces(default 'http://my.domain/cat1/')

then you don't have to use namespace prefix.

Example without default namespace

declare
  v_xml constant xmltype := xmltype('<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>'
);
 v_id number;
 v_value number;
begin
  select   id,   value_
    into v_id, v_value
  from xmltable(
    xmlnamespaces('http://my.domain/cat1/' as "foo"),
    '/foo:AA/foo:Element' passing v_xml
    columns
    id number path 'foo:ID',
    value_ number path 'foo:Value'
  );

  dbms_output.put_line('(v_id = ' || v_id || ')(v_value = ' || v_value || ')');
end;
/

Example with default namespace

declare
  v_xml constant xmltype := xmltype('<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>'
);
 v_id number;
 v_value number;
begin
  select   id,   value_
    into v_id, v_value
  from xmltable(
    xmlnamespaces(default 'http://my.domain/cat1/'),
    '/AA/Element' passing v_xml
    columns
    id number path 'ID',
    value_ number path 'Value'
  );

  dbms_output.put_line('(v_id = ' || v_id || ')(v_value = ' || v_value || ')');
end;
/

Example run:

SQL> @so58
(v_id = 2)(v_value = 46544)

PL/SQL procedure successfully completed.

SQL>

Solution 2

/*.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