I am trying to parse an XML file using the following query (used in a cursor function):
The problem I am facing is that my XML file contains 2 Namespaces, something like this:
I could work it out in case there is only one xmlns, by defining p_urn to be the value of the xmlns.
How I can achieve this with the extract function in the case I have 2 xmlns?
You could do this by skipping or wild-carding some or all of the elements:
-- with only p_urn_1 - wildcard inner default namespace
select extractvalue(value(p), '/*:Header/*:VersionInfo/*:Version/text()', p_urn_1) as version
from table (xmlsequence(extract(xmltype(pp_xml), '/Document/*:Header', p_urn_1))) p;
-- with only p_urn_2 - wildcard outer default namespace
select extractvalue(value(p), '/Header/VersionInfo/Version/text()', p_urn_2) as version
from table (xmlsequence(extract(xmltype(pp_xml), '/*:Document/Header', p_urn_2))) p;
-- with only p_urn_2 - skip outer element
select extractvalue(value(p), '/Header/VersionInfo/Version/text()', p_urn_2) as version
from table (xmlsequence(extract(xmltype(pp_xml), '//Header', p_urn_2))) p;
-- without either p_urn_1 or p_urn_2 - wildcard everything
select extractvalue(value(p), '/*:Header/*:VersionInfo/*:Version/text()') as version
from table (xmlsequence(extract(xmltype(pp_xml), '/*:Document/*:Header'))) p;
-- with both p_urn_1 and p_urn_2 - single wildcard (safe-ish)
select extractvalue(value(p), '/Header/VersionInfo/Version/text()', p_urn_2) as version
from table (xmlsequence(extract(xmltype(pp_xml), '/Document/*:Header', p_urn_1))) p;
But you are using several deprecated functions. You should look at XMLQuery and XMLTable, which allow multiple namespaces; though providing the namespaces dynamically isn't easy.
