IBM Books

XML Extender Administration and Programming


Updating XML data

With the XML Extender, you can update the entire XML document by replacing the XML column data, or you can update the values of specified elements or attributes.

Task overview:

  1. Ensure that you have stored the XML document in an XML table and determine what data you want to retrieve.
  2. Choose a method for updating the data in the DB2 table (casting functions or UDFs).
  3. Specify an SQL query that specifies the XML table and column to update.

Important: When updating a column that is enabled for XML, the XML Extender automatically updates the side tables to reflect the changes. However, do not update these tables directly without updating the original XML document that is stored in the XML column by changing the corresponding XML element or attribute value. Such updates can cause data inconsistency problems.

To update an XML document:

Use one of the following methods:

Use a default casting function
For each user-defined type (UDT), a default casting function exists to cast the SQL base type to the UDT. You can use the XML Extender-provided casting functions to update the XML document. Table 9 shows the provided casting functions and assumes the column ORDER is created of a different UDT provided by the XML Extender.

Example: Updates the XMLVARCHAR type, from the casted VARCHAR type assuming that xml_buf is a host variable that is defined as a VARCHAR type.

UPDATE sales_tab VALUES('123456', 'Sriram Srinivasan', 
  db2xml.XMLVarchar(:xml_buff))

Use a storage UDF
For each of the XML Extender UDTs, a storage UDF exists to import data into DB2 from a resource other than its base type. You can use a storage UDF to update the entire XML document by replacing it.

Example: The following example updates an XML document using the XMLVarcharFromFile() function:

UPDATE sales_tab
       set order = XMLVarcharFromFile('c:\dxx\samples\cmd\getstart.xml')
     WHERE sales_person = 'Sriram Srinivasan'

The preceding example updates the XML object from the file named c:\dxx\samples\cmd\getstart.xml to the column ORDER in the table SALES_TAB.

See Table 10 for a list of the storage functions that the XML Extender provides.

To update specific elements and attributes of an XML document:

Use the Update() UDF to specify specific changes, rather than updating the entire document. Using the UDF, you specify a location path and the value of the element or attribute represented by the location path to be replaced. (See Location path for location path syntax.) You do not need to edit the XML document: the XML Extender makes the change for you.

Update(xmlobj, path, value)
 

In this example:

xmlobj
Is the name of the XML column for which the value of the element or attribute that is to be updated.

path
Is the location path of the element or attribute that is to be updated. See Location path for location path syntax.

value
Is the value that is to be updated.

Example: The following statement updates the value of the <Customer> element to the character string IBM, using the Update() UDF:

UPDATE sales_tab
       set order = Update(order, '/Order/Customer/Name', 'IBM')
   WHERE sales_person = 'Sriram Srinivasan'


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]