HTML Tags in XML Update
400053Jul 15 2008 — edited Jul 18 2008I 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 <B>Test</B>
(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 <B>Test</B>')
WHERE id = 123;
It thinks, I am passing a substitution variable (think < and > 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