IBM DB2 XML Column Update and revert to null

0
votes

I have table called employee(id number, deatils XML) and deatils column is nullable.

Let us say I have inserted following data

insert into employee(1, '<xml><employee><name>Foo</name></employee></xml>');
insert into employee(2, null);
insert into employee(3, '<xml><employee><name>Bar</name></employee></xml>');

I have a IBM Optim tool which has a know bug which will not copy/restore this data into other schema because of null in second row. While extracting data will be extracted fine but insert into other schema will fail.

Work around for that is update the null value with some dummy xml, insert data and replace that dummy xml with null in traget database. I need db2 queries to do so.

I am doing

update employee set details='<xml></xml>' where details=null;

After this update i can extract data and insert into target but when I try to update the dummy xml i am getting errors.

Let us say I have tried like below (which wont work)

update employee set deatils=null where details='<xml></xml>';

Basically db2 is not able to find any records with my where clause due to xml stored as clob.

Please help!

2
xmldb2

2 Answers

0
votes

Please study this tutorial, or these two books. You need to

update employee set details=xmlparse(document('<xml></xml')) where details=null;

and then query by using XQUERY language with something like

xquery db2-fn:xmlcolumn('employee')/xml;

Please notice that XML columns are not clobs. They are specialized columns that you must handle by using the new functions and commands meant for that. Also, define proper XML indexes that can go deep into the structure of your documents, and never ever convert the XML into ordinary varchar string for WHERE clause (that will destroy performance instantly).

0
votes

null does not exist in XML. Instead you have an "empty sequence." You can test for empty sequence using fn:boolean. So if you look for an employee and don't find it you can replace those records with null.

Something like this:

UPDATE yourtable, XMLTABLE('$d/xml' passing details as "d"
   COLUMNS 
      empIsNull PATH 'if (fn:boolean(/xml/employee) then 0 else 1' ) as XMLINFO
SET yourtable.details = null
WHERE XMLINFO.empIsNull = 1