What is the alternative of EXTRACTVALUE function and how to use it?

Summary:

Hi All,

As EXTRACTVALUE function is no longer supported in Oracle SQL, the Data Models designed using EXTRACTVALUE is throwing error. The alternative is suggested as XMLTABLE but I am not able to use it successfully. Please help rewrite the below query:

    SELECT

        TRANSACTION_DATA_ID,

        CASE WHEN EXISTSNODE(DATA_CACHE_XML,'/TRANSACTION/TransCtx/per_terminate_save_ActionReason') = 1 THEN EXTRACTVALUE(DATA_CACHE_XML,'/TRANSACTION/TransCtx/per_terminate_save_ActionReason')

        WHEN EXISTSNODE(DATA_CACHE_XML,'/TRANSACTION/BUSINESSDATA/AM/TXN/EO/TerminationWhenAndWhyTEORow/ActionReasonModule/DATA') = 1 

		THEN EXTRACTVALUE(DATA_CACHE_XML,'/TRANSACTION/BUSINESSDATA/AM/TXN/EO/TerminationWhenAndWhyTEORow/ActionReasonModule/DATA')