How to find value of a particular attirbute in an xml payload in a table of database.

IN one of my project we were supposed to find out a particular value for one of the attribute in xml payload.

this was a tough task as there were 100k records which were failed,

I used the substring and instring concept to get these values.

following is the query that i have used


select substr(payload,instr(payload,'')+55,10)
as person_id from Data_logs
where 1=1
and ERROR_MESSAGE='Error'
and
LENGTH(TRIM(TRANSLATE(substr(payload,instr(payload,'')+55,10), ' +-.0123456789', ' '))) is null