optimal XML query
select
si.TRANSACTION_ID as "Transaction Id",
trunc(si.AUDIT_DATE) as "Audit Request Date",
si.AUDIT_DATE as "Audit Request Date/Time",
storedResults."WReference",
storedResults."PReference",
response."ICode",
response."IName"
FROM SI_TEST_XMLTYPE si ,
xmltable (
'/.'
PASSING xmlType(si.RESPONSEXML)
COLUMNS
"WReference" Varchar2(20) PATH 'wReference',
"PReference" Varchar2(255) PATH 'preference',
"storedResult" xmltype PATH 'storedResults/storedResult'
) storedResults,
xmltable (
-- xmlnamespaces('http://fakecompany.co.uk/test'