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!

HTML Tags in XML Update

400053Jul 15 2008 — edited Jul 18 2008
I have a unique situation (may be not that unique). I want to update or add HTML tags in an XML element I am writing a PL/SQL Stored Procedure to insert, update or delete elements/attributes from an XML Type column based on the input XML (coming from Java application). SP is called from Java app. For example my XML may loook like

<Description Name="Sales Message">
<Text>This is a test</Text>
</Description>

I can update this in XML by creatinga SP which accepts a value (clob or text) and make a simple update call like

UPDATE table_name
SET hotel_xml = UPDATEXML (hotel_xml, Description[@Name=''Sales Message'']/Text/text()', 'This is a Test')
WHERE id = 123;

Hopwever when I have HTML tags in my parameter value, my update fails. For example java pp passes me a string

This is a bold &lt;B&gt;Test&lt;/B&gt;
(without encoding it is "This is a Bold <B>Test</B>"

When I run this
UPDATE table_name
SET hotel_xml = UPDATEXML (hotel_xml, Description[@Name=''Sales Message'']/Text/text()', 'This is a bold &lt;B&gt;Test&lt;/B&gt;')
WHERE id = 123;

It thinks, I am passing a substitution variable (think &lt and &gt as sub variables) and the procedure fails.

How do I handle HTML encoded charcters in Oarcle Pl/SQL? Many of my SPs has to accept XML as clob that may contain HTML encoding. Java always encodes HTML tags in an XML so I have to pass the CLOB(xml) to a SP as it is
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 15 2008
Added on Jul 15 2008
14 comments
2,894 views