Extracting attribute values with XMLTABLE from XML with namespaces

Question

I have the following self-contained testcase: (run on Oracle 12.1 via Oracle 11.2.0.3 client)

SET SERVEROUTPUT ON
DECLARE
  xmldocX XMLType;
  r1X VARCHAR2(100);
  r2X VARCHAR2(100);
BEGIN
  xmldocX:=XMLType('<xmlZdroj>
    <zdrojdok typ="ELDP09">
      <eldp09 xmlns="http://schemas.cssz.cz/ELDP09" sqnr="1" yer="2009" typ="01" dep="660" nam="HB"></eldp09>
    </zdrojdok>
    </xmlZdroj>');

  SELECT sqnr,yer
  INTO r1X,r2X
  FROM xmltable(XMLNamespaces('http://schemas.cssz.cz/ELDP09' AS "xx"),  
    '/xx:xmlZdroj/xx:zdrojdok/xx:eldp09'
    PASSING xmldocX
    COLUMNS 
       sqnr VARCHAR2(10) PATH '@xx:sqnr',
       yer  VARCHAR2(10) PATH '@xx:yer'
       );

  DBMS_OUTPUT.PUT_LINE('result: '||nvl(r1X,'null')||'  '||nvl(r2X,'null'));
END;
/

It fails with "no data found". If I remove the xmlns specifications in the ELDP09 element and in XMLTABLE, the code works and gives the expected result. Can you please tell me how to achieve the same with the namespace present? I also tried to use the default namespace specification in XMLTABLE, but it was no use either.

score 1 · Answer 1 · answered Oct 14 '22 at 10:13

xmlns="http://schemas.cssz.cz/ELDP09" is applied to xmlZdroj/zdrojdok/eldp09 element only, its parents don't have this namespace.

And attributes have no namespace in your XML doc, so remove the namespace from their path also. You may check this question about attribute's namespaces for more details: XML namespaces and attributes

Combining altogether, you'll get:

with src(doc) as (
  select XMLType('<xmlZdroj>
    <zdrojdok typ="ELDP09">
      <eldp09 xmlns="http://schemas.cssz.cz/ELDP09" sqnr="1" yer="2009" typ="01" dep="660" nam="HB"></eldp09>
    </zdrojdok>
    </xmlZdroj>')
  from dual
)

select t.*
from src
  cross join xmltable(
    XMLNamespaces('http://schemas.cssz.cz/ELDP09' AS "xx"),  
    '/xmlZdroj/zdrojdok/xx:eldp09'
    PASSING src.doc
    COLUMNS 
       sqnr VARCHAR2(10) PATH '@sqnr',
       yer  VARCHAR2(10) PATH '@yer'
  ) t

SQNR	YER
1	2009

fiddle

Thanks for the solution, I've also modified my code accordingly and it works as well. Now I'd like to find out which of the two pieces of code (yours and mine) will be faster if run inside a packaged PL/SQL procedure. — papo308, Oct 15 '22 at 15:25

Extracting attribute values with XMLTABLE from XML with namespaces

1 Answers1