Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How To Use XMLTable On Encoded XML

littlecatJul 6 2015 — edited Jul 10 2015

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>&lt;AddressLine1&gt;3 Smith Ct&lt;/AddressLine1&gt;</Address></item>

<item><Address>&lt;AddressLine1&gt;4 Smith Ct&lt;/AddressLine1&gt;</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

This post has been answered by odie_63 on Jul 6 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 7 2015
Added on Jul 6 2015
5 comments
1,881 views