Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table_name (condition, column_name) as
select 'XYZ', xmltype('<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>')
from dual
1 rows affected

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';


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"')--INTOVARIABLE_NAME


SUCCESS


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';


XMLQUERY('DECLARENAMESPACESOAP-ENV="HTTP://SCHEMAS.XMLSOAP.ORG/SOAP/ENVELOPE/";DECLAREDEFAULTELEMENTNAMESPACE"HTTP://DUMMY.COM/AB/XYZ/G16";/SOAP-ENV:ENVELOPE/SOAP-ENV:BODY/PROCESSREPLY/NOTIFICATIONS/SEVERITY/TEXT()'PASSINGTABLE_NAME.COLUMN_NAMERETURNI


SUCCESS