Extracting Goal SuccessCriteriaText from hrc_txn_data that is greater than 4000 Characters using Ora

Summary

Extracting SuccessCriteriaText for Performance Goals in Draft or Pending Approval from hrc_txn_data using SQL when SuccessCriteriaText is having more than 4000 Characters

Content

After the Classic to Responsive Conversion, Oracle is using the  hrc_txn_data records for capturing the Goals that are in Draft of Pending Approval (prior to RUX all these details are captured in the HRG GOALS record), data_cache field within the record hrc_txn_data is holding the details of the Employee Goals in an XML formatted output. We have a requirement to extract the Details of the Goals prior to the approval for BI reporting Purpose. When we are trying to use the Oracle provided XMLDB functions like ExtractValue and/or XMLTABLE the SQL is erroring out when we have more than 4000 characters which is always the case.