Using declared temporary tables with XML data

The following examples assume that a table with company employees contains XML data. The employee data is like the following employee information:

<company name="MyFirstComany">
  <emp id="31201" salary="60000" gender="Female">
    <name>
      <first>Laura</first>
      <last>Brown</last>
    </name>
    <dept id="M25">Finance</dept>
  </emp>
</company>

The following CREATE statement creates an employee table and with an XML column.

CREATE TABLE COMPANYINFO (ID INT, DOC XML)

The following two statements create a global temporary table that can be used with the employee table. Both statements create a temporary table with the same column names and descriptions as the COMPANYINFO table. The first statement creates a temporary table using column definitions. The second statement creates a temporary table using the LIKE clause. The columns of the table have the same name and description as the columns of the specified table. The ON COMMIT DELETE ROWS clause specifies all rows of the table are deleted if no WITH HOLD cursor is open on the table when a COMMIT operation is performed.

DECLARE GLOBAL TEMPORARY TABLE INSTMPTB (ID INT, DOC XML) 
    ON COMMIT DELETE ROWS in USR_TBSP

DECLARE GLOBAL TEMPORARY TABLE INSTMPTB LIKE COMPANYINFO 
    ON COMMIT DELETE ROWS in USR_TBSP

The following DECLARE statement creates a temporary table with base table row storage of XML documents. If the XML document is smaller than the inline length specified, it is stored in the base table.

DECLARE GLOBAL TEMPORARY TABLE TEMPTB (ID INT, DOC XML INLINE LENGTH 3000) 
   ON COMMIT PRESERVE ROWS NOT LOGGED

If you are inserting large amounts of data into a global temporary table and performing queries over the data, you can create indexes over the XML data to improve performance. For example, the following CREATE INDEX statements create two indexes over XML data. The first index is over the employee ID in the XML documents. The second index is over the employee's family name.

CREATE INDEX SESSION.TEMP_IDX ON SESSION.INSTMPTB (DOC)
  GENERATE KEY USING XMLPATTERN '/company/emp/@id'
  AS SQL INTEGER

CREATE UNIQUE INDEX SESSION.TEMP_IDX2 ON SESSION.INSTMPTB (DOC)
  GENERATE KEY USING XMLPATTERN '/company/name/last'
  AS SQL VARCHAR(100)

You can create a declared global temporary table in a partitioned database environment to take advantage of database partitioning. The following DECLARE statement creates a temporary table using DOCID as the distribution key. After you add XML data to the temporary table, queries and other operations on the XML data can take advantage of the partitioned database environment.

DECLARE GLOBAL TEMPORARY TABLE INSTMPTB (ID INT, DOC XML) 
   ON COMMIT DELETE ROWS 
   IN USR_TBSP
   DISTRIBUTE BY HASH (ID)

The following XQuery expression uses the global temporary table and the COMPANYINFO table. The temporary table contains a subset of documents from the COMPANYINFO table. The XQuery expression returns the employee information from the COMANYINFO table of employees in the temporary table who are in the finance department.

 XQUERY
   for $i in db2-fn:xmlcolumn("SESSION.INSTMPTB.DOC")/company/emp
   for $j in db2-fn:xmlcolumn("COMPANYINFO.DOC ")[/company/emp/@id = $i/@id ]
   where $i/dept = "Finance" 
   return $j ;

The following INSERT statement inserts data from the temporary table into the COMPANYINFO table.

INSERT INTO COMPANYINFO FROM 
	(SELECT ID, DOC FROM SESSION.INSTMPTB)