Extract Tag value from XMLTYPE containing namespaces in Oracle

Question

I am on Oracle 18c and have an table with column as XMLTYPE which contains the value as below:

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP-ENV:Header/>
    <SOAP-ENV:Body>
        <ProcessReply xmlns="http://dummy.com/ab/xyz/g16">
            <HighestSeverity>PASS</HighestSeverity>
            <Notifications>
                <Severity>SUCCESS</Severity>
                <Source>abcd</Source>
                <Code>0000</Code>
                <Message>Success</Message>
            </Notifications>
        </ProcessReply>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

I have a Stored Procedure where I am trying to get the value at the node Severity, i.e. SUCCESS. I tried using EXTRACTVALUE keyword to get the value, but it doesn't seem to work.

Any help on the SELECT query to get the node value will be much appreciated. TIA.

Answer 1

okay, found the solution with a little bit of hit-and-trial. The challenge was that my XML has multiple namespaces which can be given as a third parameter to the extract value with space separation. So this query works for me:

SELECT EXTRACTVALUE (table_name.column_name, 'SOAP-ENV:Envelope/SOAP-ENV:Body/ProcessReply/Notifications/Severity', 'xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns="http://dummy.com/ab/xyz/g16"') INTO variable_name FROM table_name WHERE condition = 'XYZ';

Answer 2

The extractvalue() function is deprecated, and has been for a long time; you should use XMLQuery instead:

SELECT XMLQuery ('
    declare namespace SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/";
    declare default element namespace "http://dummy.com/ab/xyz/g16";
    /SOAP-ENV:Envelope/SOAP-ENV:Body/ProcessReply/Notifications/Severity/text()'
  passing table_name.column_name
  returning content).getstringval()
INTO variable_name
FROM table_name
WHERE condition = 'XYZ';

db<>fiddle

If you need multiple values from the same XML document then look at XMLTable instead; for example:

SELECT table_name.condition, x.severity, x.source, x.code, x.message
FROM table_name
CROSS APPLY xmltable (
  xmlnamespaces(
    default 'http://dummy.com/ab/xyz/g16',
    'http://schemas.xmlsoap.org/soap/envelope/' as "SOAP-ENV"
  ),
  '/SOAP-ENV:Envelope/SOAP-ENV:Body/ProcessReply/Notifications'
  passing table_name.column_name
  columns
    severity varchar2(10) path 'Severity',
    source varchar2(10) path 'Source',
    code varchar2(4) path 'Code',
    message varchar2(50) path 'Message'
) x
WHERE condition = 'XYZ';

gets

CONDITION	SEVERITY	SOURCE	CODE	MESSAGE
XYZ	SUCCESS	abcd	0000	Success

db<>fiddle