IBM Books

XML Extender Administration and Programming


Storing data

Using the XML Extender, you can insert intact XML documents into an XML column. If you define side tables, the XML Extender automatically updates these tables. When you store an XML document directly, the XML Extender stores the base type as an XML type.

Task overview:

  1. Ensure that you have created or updated the DAD file.
  2. Determine what data type to use when you store the document.
  3. Choose a method for storing the data in the DB2 table (casting functions or UDFs).
  4. Specify an SQL INSERT statement that specifies the XML table and column to contain the XML document.

The XML Extender provides two methods for storing XML documents: default casting functions and storage UDFs. Table 8 shows when to use each method.

Table 8. The XML Extender storage functions
Base type Store in DB2 as...
XMLVARCHAR XMLCLOB XMLFILE
VARCHAR XMLVARCHAR() N/A XMLFileFromVarchar()
CLOB N/A XMLCLOB() XMLFileFromCLOB()
FILE XMLVarcharFromFile() XMLCLOBFromFile() XMLFILE

Use a default casting function
For each UDT, a default casting function exists to cast the SQL base type to the UDT. You can use the XML Extender-provided casting functions in your VALUES clause to insert data. Table 9 shows the provided casting functions:

Table 9. The XML Extender default cast functions
Casting used in SELECT clause Return type Description
XMLVARCHAR(VARCHAR) XMLVARCHAR Input from memory buffer of VARCHAR
XMLCLOB(CLOB) XMLCLOB Input from memory buffer of CLOB or a CLOB locator
XMLFILE(VARCHAR) XMLFILE Only store file name

Example: The following statement inserts a casted VARCHAR type into the XMLVARCHAR type:

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

Use a storage UDF:
For each XML Extender UDT, a storage UDF exists to import data into DB2 from a resource other than its base type. For example, if you want to import an XML file document to DB2 as a XMLCLOB, you can use the function XMLCLOBFromFile().

Table 10 shows the storage functions provided by the XML Extender.

Table 10. The XML Extender storage UDFs
Storage user-defined function Return type Description
XMLVarcharFromFile() XMLVARCHAR Reads an XML document from a file on the server and returns the value of the XMLVARCHAR type.
XMLCLOBFromFile() XMLCLOB Reads an XML document from a file on the server and returns the value of the XMLCLOB type.
XMLFileFromVarchar() XMLFILE Reads an XML document from memory as VARCHAR, writes it to an external file, and returns the value of the XMLFILE type, which is the file name.
XMLFileFromCLOB() XMLFILE Reads an XML document from memory as CLOB or a CLOB locator, writes it to an external file, and returns the value of the XMLFILE type, which is the file name.

Example: The following statement stores a record in an XML table using the XMLCLOBFromFile() function as an XMLCLOB.

EXEC SQL INSERT INTO sales_tab(ID, NAME, ORDER) 
    VALUES( '1234', 'Sriram Srinivasan,
      XMLCLOBFromFile('c:\dxx\samples\cmd\getstart.xml'))

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


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