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.
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.