Database Software

I cannot get the extract function to return the value in inner nodes when the xml has multiple namespaces

Here is the sample code. I have tried a multitude of namespace parameter values with no luck.

I can retrieve the outer node - presumably because only one namespace applies to this node

declare

     XML_WITH_NS clob;

     LEVEL_1_NODE clob;

     LEVEL_2_NODE clob;

     LEVEL_3_NODE clob;

begin

     XML_WITH_NS :=

          '<Level_1 xmlns="Level_1_Namespace">

             <Level_2 xmlns="Level_2_Namespace">

                 <Level_3>Level 3 data </Level_3>

             </Level_2>

          </Level_1>';

     -- this works

     select extract(xmltype(XML_WITH_NS),'/Level_1','xmlns="Level_1_Namespace"').GETCLOBVAL() into LEVEL_1_NODE from DUAL;

     -- this doesn't work no matter what I put in the namespace

     select extract(xmltype(XML_WITH_NS),'/Level_1/Level_2','xmlns="Level_1_Namespace" xmlns="Level_2_Namespace"').GETCLOBVAL() into LEVEL_2_NODE from DUAL;

     -- this doesn't work no matter what I put in the namespace

     select extract(xmltype(XML_WITH_NS),'/Level_1/Level_2/Level_3').GETCLOBVAL() into LEVEL_3_NODE from DUAL;

     DBMS_OUTPUT.PUT_LINE('XML with Namespaces');

     DBMS_OUTPUT.PUT_LINE('LEVEL_1_NODE : ' || LEVEL_1_NODE);

     DBMS_OUTPUT.PUT_LINE('LEVEL_2_NODE : ' || LEVEL_2_NODE);

     DBMS_OUTPUT.PUT_LINE('LEVEL_3_NODE : ' || LEVEL_3_NODE);

end;

And this is the output

LEVEL_1_NODE : <Level_1 xmlns="Level_1_Namespace"><Level_2 xmlns="Level_2_Namespace"><Level_3>Level 3 data </Level_3></Level_2></Level_1>

LEVEL_2_NODE :

LEVEL_3_NODE :