Need to extract few fields from the CLOB column

Hi All,

We have a custom table where the attached XML data is being captured. In this custom table, there is a column named MESSAGE_DATA, which is of CLOB data type. From this CLOB column, we need to extract specific fields such as tns:message, tns:code, etc., for reporting purposes. Could you please share the SQL query to fetch these fields from the CLOB column?

I tried with below sql query but getting the error like "ORA-00902: invalid datatype"

select distinct x.*
from xx_clob_tab xct
,xmltable (
'/record/tns:ServiceErrorMessage/tns:message'
passing xmltype (xct.FAULT_PAYLOAD )