DB2 Version 9.7 for Linux, UNIX, and Windows

DECOMPOSE XML DOCUMENTS command

Decomposes XML documents stored in a database column. The data from the XML documents is stored in columns of relational tables based on annotations specified in an XML schema.

The DECOMPOSE XML DOCUMENTS command invokes the XDB_DECOMP_XML_FROM_QUERY stored procedure to decompose one or more XML documents from a binary or XML column using a registered and decomposition-enabled XML schema.

Authorization

One of the following groups of privileges is required:

All of the following privileges:
- INSERT privilege on all target tables referenced in the annotated schema
- SELECT privilege on the table, alias, or view containing the column holding the input documents
- SELECT, INSERT, UPDATE, or DELETE privilege, as applicable, on any table referenced by the db2-xdb:expression or db2-xdb:condition annotation
One of the following authorizations:
- CONTROL privilege on all tables referenced in the set of annotated schema documents and on the table, alias, or view containing the column holding the input documents
- DATAACCESS authority

If the VALIDATE option is specified, USAGE privilege on the XML schema is also required.

Required connection

Database

Command syntax


>>-DECOMPOSE XML DOCUMENTS IN--select-statement--XMLSCHEMA------>

                                  .-ALLOW NO ACCESS-.   
>--xml-schema-name--+----------+--+-----------------+----------->
                    '-VALIDATE-'  '-ALLOW ACCESS----'   

>--+----------------------+--+-------------------+-------------->
   '-COMMITCOUNT--integer-'  '-CONTINUE_ON_ERROR-'   

>--+------------------------+----------------------------------><
   '-MESSAGES--message-file-'

Command parameters

DECOMPOSE XML DOCUMENTS IN select-statement

The select-statement conforms to the rules of an SQL SELECT statement, and must return a result set containing 2 columns. The first column is the document identifier. Each document identifier uniquely identifies an XML document to be decomposed. The column must be of character type or be castable to character type. The second column contains the XML documents to be decomposed. The supported types for the document column are XML, BLOB, VARCHAR FOR BIT DATA, and LONG VARCHAR FOR BIT DATA. The column containing the XML documents must resolve to a column of an underlying base table, the column cannot be a generated column.

For example, the DOCID column in the following SELECT statement contains the unique identifiers for the XML documents stored in SALESDOC column.

SELECT DOCID, SALESDOC FROM SALESTAB

XMLSCHEMA xml-schema-name

xml-schema-name is the name of an existing XML schema registered with the XML schema repository to be used for document decomposition. xml-schema-name is a qualified SQL identifier consisting of an optional SQL schema name followed by a period and the XML schema name. If the SQL schema name is not specified, it is assumed to be the value of the DB2® special register CURRENT SCHEMA.

VALIDATE

Specifies that each input XML document is to be validated against xml-schema-name, then decomposed if the document is valid. If VALIDATE is not specified, input XML documents are not validated before decomposition.

If VALIDATE is not specified, it is the user's responsibility to validate the documents before calling the command. For example, the user can use XMLVALIDATE when inserting the XML documents into the column, or use an XML processor before inserting the documents. If an input XML document is not valid and VALIDATE is not specified, the decomposition results are undefined. See the related reference at the end of this topic for information about XML validation.

ALLOW

Specifies whether access to the target tables specified in the XML Schema xml-schema-name are allowed during decomposition. ALLOW NO ACCESS is the default value.

ALLOW ACCESS: If ALLOW ACCESS is specified, when acquiring locks on the target table, the DECOMPOSE operation will wait and possibly timeout.
ALLOW NO ACCESS: If ALLOW NO ACCESS specified or used as the default value, the DECOMPOSE operation will acquire an exclusive lock (X) on all tables which have mappings specified in the XML schema. Not all target tables will necessarily participate during the decomposition of each document, but all target tables will be locked to lower the possibility of deadlock during a long unit of work.

COMMITCOUNT integer

Specifies that after every integer successful document decompositions, a COMMIT is performed. A value of 0, or if the option is not specified, means that no COMMIT will ever be performed by the DECOMPOSE operation.

CONTINUE_ON_ERROR

Specifies that the DECOMPOSE operation continues to the next document if a document-specific error occurs. Any changes to the database caused by an unsuccessfully decomposed document is undone before proceeding to the next document. If CONTINUE_ON_ERROR is not specified, the DECOMPOSE operation stops on the first document that cannot be successfully decomposed.

The DECOMPOSE operation does not continue on fatal errors and non-document specific errors even if the CONTINUE_ON_ERROR option is specified.

MESSAGES message-file

The DECOMPOSE operation generates a UTF-8 encoded XML document that lists the input XML documents that were not successfully decomposed, along with the reason for their failure. The document containing the decomposition errors is generated only if there is at least one XML document that could not be successfully decomposed. Messages are translated according to server locale. message-file is the file that contains the XML document containing the decomposition information. If message-file is specified the file will be created on the system from where the CLP command is invoked. If the complete path of the file is not specified, it will be created in the current directory.

If this option is not specified, the decomposition information will be written to standard output.

Information about the decomposition of XML documents is displayed as an XML document that can optionally be sent to message-file specified by the parameter MESSAGES. The format of the XML document in message-file is as follows:

<?xml version='1.0' xmlns:xdb="http://www.ibm.com/xmlns/prod/db2/xdb1"?>
<xdb:errorReport>
   <xdb:document>
      <xdb:documentId>sssss</xdb:documentId>
      <xdb:errorMsg>qqqqq</xdb:errorMsg>
   </xdb:document>
   <xdb:document>
      . . . 
   </xdb:document>
   . . . 
</xdb:errorReport>

The documentId value sssss is the value from the first column specified by select-statement. The value identifies the XML document that was not successfully decomposed. The errorMsg value qqqqq is the error encountered during the attempt to decompose the document.

Example

You could insert XML documents to be decomposed into a relational table, for example: ABC.SALESTAB. All the documents correspond to an XML schema registered as ABC.SALES, and the schema has been annotated with decomposition information and enabled for decomposition. Assuming the column name into which the documents are inserted is SALESDOC, and the corresponding ID is inserted into DOCID, invoke the DECOMPOSE XML DOCUMENTS command as follows:

DECOMPOSE XML DOCUMENTS IN 'SELECT DOCID, SALESDOC FROM SALESTAB'
  XMLSCHEMA ABC.SALES 
  MESSAGES /home/myid/errors/errorreport.xml