Tuesday, August 4, 2009

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

12 Comments:

mrwest said...

Thanks Apun, the Update XML data code worked perfectly for me!

JACK said...

Hi Apun

I am facing issue while updating the xml data when it is null
example -

SELECT EXTRACTVALUE(XMLTYPE.createXML(clob),'/Message/Entity/ASSIGNMENT/OAVendorLocation/text()') XMLSrc
from T_ORG

return (null)

UPDATE T_ORG set clob =
UPDATEXML(XMLTYPE.createXML(clob),'/Message/Entity/ASSIGNMENT/OAVendorLocation/text()','22121').getClobVal()

return 1 row updated
but when you run select query again it shows (null)

The same lines of code working fine if an existing value replaced with new value.

I am stuck in my code.

Apun Hiran said...

I dont have a place to test this, but try giving the complete element/path:

something like: UPDATE T_ORG set clob = UPDATEXML(XMLTYPE.createXML(clob),'/Message/Entity/ASSIGNMENT/OAVendorLocation/text()','22121').getClobVal()

Please let me know if this works.
Regards
Apun

JACK said...

Hi Apun,
Thanks for the response.
My table is like
Name varchar2(256)
Vendor Varchar2(256)
clob clob -> contains XML data.

XML data likes

Message>
Entity>
ASSIGNMENT>
OAVendorLocation> OAVendorLocation
Vendorid>1 Vendorid
ASSIGNMENT
Entity
Message

Tags <> are not closed here because data was not showing here correctly.Imagine all tags as closed.

So again if I want to update vendorid it is up datable with


UPDATE T_ORG set clob =
UPDATEXML(XMLTYPE.createXML(clob),'/Message/Entity/ASSIGNMENT/Vendorid/text()','221').getClobVal()

but OAVendorLocation is not updating as it contains null value currently.

I am putting complete path.
Please help.

Regards,
Jack

Apun Hiran said...
This comment has been removed by the author.
Apun Hiran said...

When I posted by previous comment I did not notice that blogger removes the tag in the comment.
Please remove space in the update command where the tag is specified.

something like:
UPDATE T_ORG set clob = UPDATEXML(XMLTYPE.createXML(clob),'/Message/Entity/ASSIGNMENT/OAVendorLocation/text()','< OAVendorLocation >22121< /OAVendorLocation >').getClobVal()

Please let me know if this works.
Regards
Apun

JACK said...

Hi Apun,

I got the solution for this as I have to use Appendchildxml here to update the non exist text.

I have one more question here is how can I check the existence of tag inside an xml clob data file ?
This I need when I dont find the xml tag in clob data and needed to add on request.

like -
Message>
Entity>
ASSIGNMENT>
OAVendorLocation> OAVendorLocation
Vendorid>1 Vendorid
ASSIGNMENT
Entity
Message

Here I wanted to check VendorAddress tag exist in the file or not ?

As it is not there, I will add it on request.

Thanks & Regards,
Jack

Apun Hiran said...

You can try existsnode, eg:
SQL> select from T_ORG where
2 existsnode(XMLTYPE.createXML(clob),'/Message/Entity/ASSIGNMENT/OAVendorLocation/VendorAddress')=1;

A
----------
1

You can put this in a if condition, if 1 ignore if 0 update.

You can correct the path as per your xml data.

Regards
Apun

Jack said...

Hi Apun

I have another case where I have too add new tag in my xml like
current ->
Message>
Entity>
ASSIGNMENT>
OAVendorLocation> OAVendorLocation
Vendorid>1 Vendorid
ASSIGNMENT
Entity
Message

After addition new tag job

Message>
Entity>
ASSIGNMENT>
OAVendorLocation> OAVendorLocation
Vendorid>1 Vendorid
job>IT job
ASSIGNMENT
Entity
Message

How can I add new tag with some data ? I tried to add with appenchildxml but its not working.

Please help
Jack

Anonymous said...

Apun,
Let me know how to ltrim rtim the exits data in xml element in CLOB column.
Thanks,
Chidam Alagar
Chidam.Alagar@gmail.com

Rajath said...

Hi Apun

Very well explained with simple example

Helped a lot!

Thanks
Rajath

Anonymous said...

Thanks for writing this blog. It helped me a lot today.