How to fetch data from transaction console table for ICP entry which is not approved ?

We are trying to fetch details of ICPs which are in submitted status(not yet approved).The details which we are trying fetch is person details, ICP type, option , date worked , hours worked etc. Since, it is not yet approved so no element entry has been created for it. The data is currently in column xml_data_cache of table hrc_txn_header. But it is in json format. Have anyone worked on fetching details for bip report from xml_data_cache column and guide us for the same.

Also, when an employee submits more than 1 ICP together. It creates data against single transaction_id in table hrc_txn_header. How to split it into multiple rows?