IBM Books

XML Extender Administration and Programming

Retrieving an entire document

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 retrieving the data in the DB2 table (casting functions or UDFs).
  3. If using the overloaded Content() UDF is to determine which data type is associated with the data that is being retrieved and which data type is to be exported.
  4. Specify an SQL query that specifies the XML table and column from which to retrieve the XML document.

The XML Extender provides two methods for retrieving data:

Use a default casting function
Use the default casting function provided by DB2 for UDTs to convert an XML UDT to an SQL base type, and then operate on it. You can use the XML Extender-provided casting functions in your SELECT statement to retrieve data. Table 12 shows the provided casting functions:


Table 12. The XML Extender default cast functions
Casting used in select clause Return type Description
varchar(XMLVARCHAR) VARCHAR XML document in VARCHAR
clob(XMLCLOB) CLOB XML document in CLOB
varchar(XMLFile) VARCHAR XML file name in VARCHAR
Example: The following example retrieves the XMLVARCHAR and stores it in memory as a VARCHAR data type:

EXEC SQL SELECT db2xml.varchar(order) from sales_tab

Use the Content() overloaded UDF
Use the Content() UDF to retrieve the document content from external storage to memory, or export the document from internal storage to an external file on the DB2 server.

For example, you might have your XML document stored as XMLFILE and you want to operate on it in memory, you can use the Content() UDF, which can take an XMLFILE data type as input and return a CLOB.

The Content() UDF performs two different retrieval functions, depending on the specified data type. It:

Retrieves a document from external storage and puts it in memory
You can use Content() to retrieve the XML document to a memory buffer or a CLOB locator when the document is stored as the external file. Use the following function syntax, where xmlobj is the XML column being queried:

XMLFILE to CLOB: Retrieves data from a file and exports to a CLOB locator.

Content(xmlobj XMLFile)

Retrieves a document from internal storage and exports it to an external file
You can also use Content() to retrieve an XML document that is stored inside DB2 as an XMLCLOB data type and export it to a file on the database server file system. It returns the name of the file of VARCHAR type. Use the following function syntax, where xmlobj is the XML column that is being queried and filename is the external file. XML type can be of XMLVARCHAR or XMLCLOB data type.

XML type to external file: Retrieves the XML content that is stored as an XML data type and exports it to an external file.

Content(xmlobj XML type, filename varchar(512))

Where:

xmlobj
Is the name of the XML column from which the XML content is to be retrieved; xmlobj can be of type XMLVARCHAR or XMLCLOB.

filename
Is the name of the file in which the XML data is to be stored.

In the example below, a small C program segment with embedded SQL illustrates how an XML document is retrieved from a file to memory. This example assumes that the column BOOK is of the XMLFILE type.

EXEC SQL BEGIN DECLARE SECTION;
              SQL TYPE IS CLOB_LOCATOR xml_buff;
          EXEC SQL END DECLARE SECTION;
          EXEC SQL CONNECT TO SALES_DB
          EXEC SQL DECLARE c1 CURSOR FOR
              SELECT Content(order) from sales_tab 
                        EXEC SQL OPEN c1;
          do {
             EXEC SQL FETCH c1 INTO :xml_buff; 
             if (SQLCODE != 0) {
                break;
                }
             else {
               /* do whatever you need to do with the XML doc in buffer */
               }
             }
          EXEC SQL CLOSE c1;
          EXEC SQL CONNECT RESET;


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