Edit XML stored as CLOB

Question

I have a very long XML document stored in an Oracle database as CLOB type. In my SQL tools I can't even copy out the complete XML data because it is too long (I am only able to copy out part of the XML).

Say I want to edit item code="00000012" to "00000011", how can I do that?

My logic is to extract out the entire XML content from the CLOB column, modify it in a text editor, and update it using a query.

The following is just short representation of my XML, in the database the size is larger.

<ExampleMapping><item code="00000012" item name="Test1"/><item code="00000013" item name="Test2"/></ExampleMapping>

In my DB the table name is TableX and the the columns are ID and xmlData.

score 0 · Accepted Answer · edited May 23 '17 at 12:06

Copying the current value out to a text editor, modifying it, and then trying to update the value using your new string is going to be painful. If it's more than 4000 characters (which is likely if you're using a CLOB for storage) you would have to do it in PL/SQL; and if it's more than 32K you would have to create and update a temporary CLOB in chunks, which means splitting your string up.

You can do it through SQL though, if you convert your CLOB to XMLType first. You can use the updatexml function for this, with a suitable XPath argument to match the row and attribute you're interested in:

select updatexml(xmltype(t.xmldata), '//item[@code="00000012"]/@code', '00000011').getclobval()
from tablex t
where dbms_lob.compare(xmldata, empty_clob()) != 0
and xmlexists('//item[@code="00000012"]' passing xmltype(xmldata));

UPDATEXML(XMLTYPE(T.XMLDATA),'//ITEM[@CODE="00000012"]/@CODE','00000011')
--------------------------------------------------------------------------------
<ExampleMapping><item code="00000011" name="Test1"/><item code="00000013" name="Test2"/></ExampleMapping>

I've used a modified version of your string, removing the extra 'item' before each 'name' to make it valid XML. I've also converted the result back to a CLOB as it seems your client can't handle XMLType.

To update the row in the table instead of just selecting it:

update tablex
set xmldata = updatexml(xmltype(xmldata), '//item[@code="00000012"]/@code', '00000011').getclobval()
where dbms_lob.compare(xmldata, empty_clob()) != 0
and xmlexists('//item[@code="00000012"]' passing xmltype(xmldata));


1 row updated.

select * from tablex;

        ID XMLDATA                                                              
---------- ---------------------------------------------------------------------
         1 <ExampleMapping><item code="00000011" name="Test1"/><item code="00000013" name="Test2"/></ExampleMapping>
         2 <ExampleMapping><item code="00000014" name="Test3"/><item code="00000015" name="Test4"/></ExampleMapping>
         3 
         4

This uses xmlexists to only apply the update to matching rows, so you don't touch the rows that don't really need to change. There are four rows in my dummy table, including one that is null and one which is an empty_clob (which has to be excluded with the dbms_lob.compare); but only one row was updated.

To remove an item completely, use the deletexml function:

update tablex
set xmldata = deletexml(xmltype(xmldata), '//item[@code="00000014"]').getclobval()
where dbms_lob.compare(xmldata, empty_clob()) != 0
and xmlexists('//item[@code="00000014"]' passing xmltype(xmldata));

1 row updated.

select * from tablex;

        ID XMLDATA                                                              
---------- ---------------------------------------------------------------------
         1 <ExampleMapping><item code="00000011" name="Test1"/><item code="00000013" name="Test2"/></ExampleMapping>
         2 <ExampleMapping><item code="00000015" name="Test4"/></ExampleMapping>
         3 
         4

If you want to match more than one attribute (unclear from your comment) you can use and in the XPath: '//item[@code="00000014" and @name="Test3"]' (in both places). Also I'm being lazy using //item; it's better to use the full path really, /ExampleMapping/item[...].

Read more about manipulating XML in the documentation.

Hi, first of all thank you for your reply. I tried the 2nd way to update my xml, but it has error during xml parsing and not sure why: ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00229: input source is empty ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: at line 1 — hades, Aug 04 '15 at 09:58
and i also tried the first select query, it tells me: "The query fails because all columns types are currently not supported." My version of oracle is 11g Enterprise Edition Release 11.2.0.4.0 — hades, Aug 04 '15 at 10:00
@user3172596 - the 'query fails' message sounds like it's coming from your client (what is it?), maybe because it can't handle the XMLType; maybe try it with the `.getclobval()`? — Alex Poole, Aug 04 '15 at 10:15
@user3172596 - for the update, do you have any rows in your table where `xmldata` is null or empty? — Alex Poole, Aug 04 '15 at 10:21
is the updatexml independent on different oracle sql tools? because the intellisense of SQLTools cant detect the keyword updatexml — hades, Aug 04 '15 at 10:27
No, `updatexml` is a built-in function. I've updated both statements in the answer to check for empty CLOBs. — Alex Poole, Aug 04 '15 at 10:30
Hi, one more question, how can i remove 1 set of the xml? I want to remove — hades, Aug 04 '15 at 10:43
@user3172596 - different question really, but I've added an example, and a link to docs covering XML modification in general. — Alex Poole, Aug 04 '15 at 11:18

Edit XML stored as CLOB

1 Answers1