Oracle XML DB with XMLEXISTS don't work with xmlns on root XML

373 Views Asked by At

I have a table with XMLTYPE column and the XMLEXIST works fine.

But when the root element have a xmlns atribute the XMLEXISTS don't find the record what i'm looking for.

Without the xmlns attribute the XMLEXISTS works fine.

What's wrong?

Example:

<employe>
   <employe_id>12345</employe_id>
   <employe_name>John</employe_name>
</employe>

The Query:

SELECT count(*) FROM mytable
WHERE XMLEXISTS('/employe[employe_id="12345"]' 
                PASSING xmltype_col
      )

Result: 1

But with xmlns attribute on the root element the query return 0.

<employe xmlns="http://www.example.com/version_01_01_00">
   <employe_id>12345</employe_id>
   <employe_name>John</employe_name>
</employe>

The mytable is schemaless.

1

There are 1 best solutions below

0
Mário Monteiro On

After much research, I discovered the solution.

select *
from (
select
   xmlelement("employe",
     XMLATTRIBUTES('www.example.com/version_01_01_00' as "xmlns") ,
      xmlforest(
         '12345' as "employe_id",
         'John'  as "employe_name"
         )   
    ) test
from dual
) x
where XMLEXISTS(
'declare default element namespace "www.example.com/version_01_01_00"; (::)
/employe[employe_id="12345"]'
                PASSING test
      )