XML Update??

[ssahil11]

What is the query to update an element in XML document?

[dilasing]

DB2 9 does not support updating an element in XML documents, you must perform a full-document update using the UPDATE SQL statement.

UPDATE customer SET info =
'<customerinfo xmlns="http://posample.org" Cid="1002">
<name>Jim Noodle</name>
<addr country="Canada">
<street>1150 Maple Drive</street>
<city>Newtown</city>
<prov-state>Ontario</prov-state>
<pcode-zip>Z9Z 2P2</pcode-zip>
</addr>
<phone type="work">905-555-7258</phone>
</customerinfo>'
WHERE XMLEXISTS (
'declare default element namespace "http://posample.org";
$doc/customerinfo[@Cid = 1002]'
passing INFO as "doc")

The XMLEXISTS predicate ensures that only the document containing the attribute Cid="1002" is replaced. Notice how the predicate expression in XMLEXISTS, [@Cid = 1002], is not specified as a string comparison: [@Cid = "1002"]. This is because the index, previously created, for the Cid attribute was defined with the DOUBLE data type. In order for the index to match this query, Cid cannot be specified as a string in the predicate expression.

MTK can be downloaded at
http://www-306.ibm.com/software/data/db2/migration/mtk/. More information can be found at
http://www-1.ibm.com/support/docview.wss?rs=203&uid=swg27009230.

[ssahil11]

I am working on DB2 9; I want to update XML sub-document. Is there any way to update a particular element value?

[dilasing]

XML documents are stored natively in DB2 and there is no out-of-the-box functionality for performing sub-document updates.

One solution to this problem is to bring the document over the client, modify it, and then save it back into the database. This approach is limited by the XML capabilities of the client environment.

There is another solution. By creating an update stored procedure it is possible to update XML documents in the database without having to bring them over the client. This stored procedure enables partial updates of XML documents.

For example, DB2XMLFUNCTIONS.XMLUPDATE is a java-based stored procedure. The following example updates XML document by replacing the simple name element with a complex name element.

Call DB2XMLFUNCTIONS.XMLUPDATE (
'<updates namespaces="x:http://posample.org">
<update action="replace" col="1" path="/x:customerinfo/x:name">
<name><fname>Hardeep</fname><lname>Singh</lname></name>
</update>
</updates>',
'Select info from CUSTOMER where cid=1006',
'update CUSTOMER set info=? where cid=1006',?,?);

You can download jar file and examples at:
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0605singh/

[ssahil11]

How to setup and register the db2xmlfunctions.XMLUPDATE java based update store procedure in DB2 9?

[dilasing]

Steps to setup the update stored procedure:

i) Download db2xmlfunctions.jar file and source code from http://www.ibm.com/developerworks/views/download.jsp?contentid=111244&filename=db2xmlfunctions.jar&method=http&locale=worldwide

ii) On Microsoft Windows, open a DB2 command window:

SET CLASSPATH= .;%DB2PATH%\java\db2java.zip;
%DB2PATH%\java\db2jcc.jar;
%DB2PATH%\java\db2jcc_license_cisuz.jar;
"%DB2PATH%\java\jdk\bin\javac.exe" -d . *.java
"%DB2PATH%\java\jdk\bin\jar" cvf db2xmlfunctions.jar com/ibm/db2/xml/functions/*.class

iii) Install the stored procedure in DB2
DB2 -t
connect to your_dbname;
CALL SQLJ.INSTALL_JAR('file:/temp/samples/db2xmlfunctions.jar' ,
db2xmlfunctions,0);
iv) Register the stored procedure in your database:

CREATE PROCEDURE db2xmlfunctions.XMLUPDATE(
IN COMMANDSQL VARCHAR(32000),
IN QUERYSQL VARCHAR(32000),
IN UPDATESQL VARCHAR(32000),
OUT errorCode INTEGER, OUT errorMsg VARCHAR(32000))
DYNAMIC RESULT SETS 0
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
NULL CALL MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME b2xmlfunctions:com.ibm.db2.xml.functions.XMLUpdate.Update';
TERMINATE;

You can read more at:
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0605singh/

[ssahil11]

What are the uses of db2xmlfunctions.jar java based xml update stored procedure? Does anyone have idea about XMLUPDATE function, especially the syntax?

[dilasing]

The java based xml update stored procedure can be used for:

i) Changing the value of any text or attribute nodes in the target XML document.
ii) Replacing an element node in the XML document with another XML element along with all it child nodes.
iii) Deleing a node in the XML document.
iv) Inserting a new element.
v) Multiple updates to the source documents.
vi) Updates to multiple source documents.
vii) Replacing another XML document with the modified one.
viii) Inserting the modified document into a new record.

The update information can be:

i) Statically embedded in the update call
ii) Dynamically created at runtime using SQL.
iii) Computed using an arithmetic expression on the original text or attribute value.

The syntax of XMLUPDTE function is:

DB2XMLFUNCTIONS.XMLUPDATE (commandXML, querySQL, updateSQL, errorCode, errorMsg)

· commandXML à this argument is an XML string that encapsulates the update commands. These commands are then applied to the XML documents selected by the querySQL.
The structure of this command is:
<updates namespaces="">
<update using="" col="" action="" path="">update value</update>
</updates>
· querySQL à any valid SQL select statement that retrieves the XML documents that need to be update.
· updateXQL à It represents a parameterized update SQL. The modified XML document is bound to the update SQL as a runtime parameter. It allows a modified XML document to be saved to other XML columns in the database.
· errorCode à a value of -1 indicates that the stored procedure was aborted due to some error. If the update was successful, a positive value indicating the number of records that have been updated is returned.
· errorMsg à error message, including any exception thrown by the XML parse and the JCC driver.

You can find more information at:
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0605singh/

[ssahil11]

I made some changes to db2 xml update stored procedure. I want to remove this. How do I remove the store procedure in DB2?

[dilasing]

If you make any changes to the stored procedure, you should first drop the store procedure from DB2 and then call remove stored procedure jar statement. You can use below steps to remove stored procedure.

//dropping db2 xml update stored procedure.
DROP PROCEDURE DB2XMLFUNCTIONS.XMLUPDATE(VARCHAR(32000),
VARCHAR(32000),VARCHAR(32000),INTEGER, VARCHAR(32000));

//removing xml update java based stored procedure jar.
CALL SQLJ.REMOVE_JAR(DB2XMLFUNCTIONS);

[ssahil11]

How can I fix Out Of Memory error while executing XML updates stored procedure?

[dilasing]

This exception is typically found only on 32-bit JVMs. Native memory leak is the reason for java.lang.OutOfMemoryError exception while running XML update stored procedure. The java heap size should be updated to fix this problem.

The below command updates java heap size to 1024.

db2 update dbm cfg using JAVA_HEAP_SZ 1024

[wrestle23]

execute the source code mate

[ssahil11]

I am not able to add a new element to XML document using XMLUPDATE stored procedure. I am not sure if I am missing something. Can someone give a working sample?

[dilasing]

Suppose that we have to add a new e-email address for clients. Below example shows how to add a new element in an XML document using the XMLUPDATE stored procedure.

CALL DB2XMLFUNCTIONS.XMLUPDATE(
'<updates>
<update action="append" col="1" path="/addr">
<Email>[e-mail]</Email>
</update>
</updates>',
'select doc from CLIENTS where id=77',
'update CLIENTS set contactinfo=? where id=77', ?, ?);

i) action=”append” : we add new element
ii) col=”1” : we work with the first XML column returned by querySQL.
iii) path=”/addr” : the element we add will be a child to this element.
iv) <Email>[e-mail]</Email> : the new element
v) ‘select doc from CLIENTS where id=77’ : Our querySQL
vi) ‘update CLIENTS ser cotactinfo=? Where id=77’ : Our updateSQL

[ssahil11]

How can I validate xml document against an xml schema while updating an existing xml data in the database using update stored procedure?

[dilasing]

First you need to create and register xml schema. Here, consider test.schema xml schema has been registered.

In the below example we are trying to update xml document stored in info column and validate the updated document against the test.schema2 xml schema.

Call DB2XMLFUNCTIONS.XMLUPDATE (
'<updates namespaces="x:http://posample.org">
<update using="sql" action="replace" col="1"
path="/x:customerinfo/x:addr/x:pcode-zip/text()">
select cid from Customer where cid=1004
</update>
</updates>',
'Select info from Customer where cid=1004',
'update Customer set info=xmlvalidate(
? according to XMLSCHEMA ID test.schema) where cid=1004',?,?)