I have case of external web service providing response which is XML, but contains encoded XML within.
I wish to use XMLTable to extract results. I am familiar with XMLTable, but cannot get to work with mixed encoded/unencoded XML. This returns no data. If all unencoded, then returns data as required. I assume I need to convert somehow, but not sure how to achieve this.
Code snippet shown below
declare
vResponse XMLType;
begin
vResponse:= XMLTYPE(
'<ValAddrResult>
<Addresses>
<item><Address><AddressLine1>3 Smith Ct</AddressLine1></Address></item>
<item><Address><AddressLine1>4 Smith Ct</AddressLine1></Address></item>
</Addresses>
</ValAddrResult>');
for c1 in
(SELECT x.*
FROM XMLTABLE ('/ValAddrResult/Addresses/item/Address'
passing vResponse
COLUMNS AddressLine1 VARCHAR2(500) PATH 'AddressLine1') x)
loop
dbms_output.put_line(c1.AddressLine1);
end loop;
end;
Thanks