Insert/Update XML data stored in CLOB Column

Recently I had a requirement to insert/update XML data stored directly in CLOB column of a table by the application.
There was a requirement to update the XML node values directly by-passing the application. I had no experience with using XML function provided by oracle, plus the column is a normal CLOB column, as part of the many columns the table has. Let me give you an example for such a table:

Columns Name Data Type
--------------------------------------------------------------------------------
ID Number
First_Name VARCHAR2(128)
Last_Name VARCHAR2(128)
DETAILS CLOB ---> This column stores the XML information.

The process that we need to follow:

Convert the CLOB column to XMLTYPE using XMLTYPE.createXML(column_name)
e.g. XMLTYPE.createXML(details)
The important thing is to know the XML pattern in the column. eg.


<details>

<profile>

<websiteurl><![CDATA[www.yahoo.com]]></websiteurl>

<companyname><![CDATA[Google]]></companyname>

<jobtitle><![CDATA[DBA]]></jobtitle>

<addresses>

<address find="addressType">

<addresstype>Address1</addresstype>

<streetline1><![CDATA[123 XYZ 1111]]></streetline1>

</address>

<address find="addressType">

<addresstype>Address2</addresstype>

<streetline1><![CDATA[890 abc 2222]]></streetline1>

</address>

</addresses>

<phonenumbers>

<phonenumber find="phoneType">

<phonetype>Phone1</phonetype>

<number><![CDATA[+1-111-111-1111]]></number>

</phonenumber>

<phonenumber find="phoneType">

<phonetype>Fax1</phonetype>

<number><![CDATA[222-222-2222]]></number>

</phonenumber>

<phonenumber find="phoneType">

<phonetype>Mobile1</phonetype>

<number><![CDATA[+1-333-333-3333]]></number>

</phonenumber>

</phonenumbers>

<emails>

<email find="emailType">

<emailtype>Email1</emailtype>

<email><![CDATA[hero@abc.com]]></email>

</email>

<email find="emailType">

<emailtype>Email2</emailtype>

<email><![CDATA[hero1@abc.com]]></email>

</email>

</emails>

</profile>

</details>




Now queries to SELECT data from the XML column:

SQL> SELECT EXTRACTVALUE(XMLTYPE.createXML(details),'/details/profile/companyName/text()') XMLSrc FROM usera WHERE id='1';
XMLSRC
--------------------------------------------------------------------------------
Google
The above query will give you the extact value of the company.

SQL> SELECT EXTRACT(XMLTYPE.createXML(details),'/details/profile/companyName/text()') XMLSrc FROM usera WHERE id='1';
XMLSRC
--------------------------------------------------------------------------------

The above query returns the complete of the node.

SQL> SELECT EXTRACTVALUE(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']/streetLine1/text()') XMLSrc FROM usera WHERE id='1';
XMLSRC
--------------------------------------------------------------------------------
123 XYZ 1111

The above example shows how to query data inside a nested node. For me it was easy to query any node directly, but I had to really dig in to find out how to query a node which is for a particular type of a parent node.

Now to Update some data:
SQL> UPDATE usera set details = UPDATEXML(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']/streetLine1/text()','The World is Great').getClobVal() WHERE id=1;

1 row updated.

SQL> SELECT EXTRACTVALUE(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']/streetLine1/text()') XMLSrc FROM usera WHERE id='1';
XMLSRC
--------------------------------------------------------------------------------
The World is Great

If you look at the above update, the details column was first converted into XMLTYPE and then the node updated and then returned back as CLOB using getClobVal() function as the column is CLOB type not XMLTYPE. If you do not use the getClobVal() function you would get the following error:

SQL> UPDATE usera set details = UPDATEXML(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']/streetLine1/text()','The World is Great') WHERE id=1;
UPDATE usera set details = UPDATEXML(XMLTYPE.createXML(details),'/details/profile/addresses/address[
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CLOB got -

Now to add data into the XML:

SQL> UPDATE usera set details = APPENDCHILDXML(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']',XMLTYPE ('xyz garbage')).getClobVal() WHERE id=1;

1 row updated.

SQL> SELECT EXTRACTVALUE(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']/streetLine2/text()') XMLSrc FROM usera WHERE id='1';
XMLSRC
--------------------------------------------------------------------------------
xyz garbage

In the append data section, we need to provide the path and then the XML value to add.

A small test procedure to check if the value exists or not and then append.
declare
var1 clob;
begin
SELECT EXTRACTVALUE(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']/streetLine2/text()') into var1 FROM usera WHERE id=1;
IF var1 is NULL then
UPDATE usera set details = APPENDCHILDXML(XMLTYPE.createXML(details),'/details/profile/addresses/address[addressType=''Address1'']',XMLTYPE ('xyz garbage')).getClobVal() WHERE id=1;
END IF;
end;

To look for more XML functions please refer to page Oracle XML Functions