Extract Xml Value From Clob

I have the following xml stored in a CLOB column in a table. I want to retrieve one value from this xml - ReplacementPersonId. I tried using the following query but it is not wor

Solution 1:

Try like this,

SELECT EXTRACTVALUE(XMLTYPE.createxml(transaction_document), '/Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow/ReplacementPersonId')
FROM   hr_api_transactions 
WHERE  LENGTHB(TO_CHAR(SUBSTR(transaction_document,1,4000)))<>0;