Friday, October 2, 2015

db2 xml update command

Recently I needed to update db2 column based on XML extract from another field. By surprise, this is not documented very well. Here is how you can do it using SQL statement - make sure your db2 level is 9+:

update ibmcom.docstore set language = XMLCAST ( XMLQUERY('$c/externalMetaData/field[@name="Language"]/text()' passing EXTERNAL_METADATA as "c") AS VARCHAR(5))

Note that this script extracts XML node from XML field, which in my case looks similar to this:
<externalMetaData><field name="ContentType">text/html</field><field name="docid"></field><field name="documentSource">pushAPI</field><field name="Language">en</field><field name="keywords">websphere</field><field name="Charset">UTF-8</field><field name="scopes">my</field></externalMetaData>

The language field is then extracted from XML and stored in appropriate column. The real trick is in the XMLCAST function, as if you do not use it, the UPDATE statement will fail.

No comments:

Post a Comment