How to get the namespace value in a xmltype tag?

Question

I am working with XmlType in oracle 11g, and i have a tag like this:

**<ProprietaryId Namespace="CMA_ID">ad017f3a9736ce9d8cbbfc89955aa033 </ProprietaryId>**

I would like to know how to get the information of the "NameSpace" ?

Here is the code i am using:

SELECT extractvalue( value (sr),'*/ReleaseId/ProprietaryId' )as ProprietaryId
  FROM XML_TABLE X,
       table(xmlsequence (extract(dados, '*/ContainedReleaseList/Release' )))sr

But i can only get the information of the tag and not the name space.

Answer 1

You need to use @ to specify the attribute, whose value you need.

Here's a sample query.

SQL> with x(y) as (
select '<?xml version="1.0" encoding="UTF-8"?>
<service_orders count="1">
      <ProprietaryId Namespace="CMA_ID">ad017f3a9736ce9d8cbbfc89955aa033 </ProprietaryId>
</service_orders>' from dual
)
select
    extractvalue(xmltype(y),'service_orders/ProprietaryId/@Namespace')  as ProprietaryNamespace
from
    x;

PROPRIETARYNAMESPACE
-----------------------
CMA_ID

But EXTRACTVALUE is deprecated. You can use XMLTABLE for this.

SQL> with x(y) as (
select '<?xml version="1.0" encoding="UTF-8"?>
<service_orders count="1">
      <ProprietaryId Namespace="CMA_ID">ad017f3a9736ce9d8cbbfc89955aa033 </ProprietaryId>
</service_orders>' from dual
)
select
    prop.ProprietaryNamespace
from
    x,
    xmltable('service_orders/ProprietaryId'
        passing xmltype(x.y)
        columns ProprietaryNamespace varchar2(20) path '@Namespace'
        ) prop;

PROPRIETARYNAMESPACE
--------------------
CMA_ID