The stored procedure decomposes one or more XML documents
from a binary or XML column. The data from the XML documents is stored
in columns of relational tables based on annotations specified in
an XML schema.
Syntax
>>-XDB_DECOMP_XML_FROM_QUERY--(--rschema--,--xmlschema--,------->
>--query--,--validation--,--commit_count--,--allow_access--,---->
>--reserved--,--reserved2--,--continue_on_error--,-------------->
>--total_docs--,--num_docs_decomposed--,--result_report--)-----><
The schema of the stored procedure is SYSPROC.
The procedure executes with the read stability isolation
level.
Authorization
One of the following authorizations or 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
- 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 value of validation is
1, USAGE privilege on the XML schema is also required.
Procedure parameters
- rschema
- An input argument of type VARCHAR(128) that specifies the SQL
schema part of the two-part XML schema repository (XSR) object name
registered with the XML schema repository. The value can be NULL.
If the value is NULL, the SQL schema part is assumed to be the current
value of the CURRENT SCHEMA special register.
- xmlschema
- An input argument of type VARCHAR(128) that specifies the name
of the two-part XSR object name registered with the XSR. This value
cannot be NULL.
- query
- An input argument of type CLOB(1MB).
This value cannot be NULL. The query 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
- validation
- An input argument of type INTEGER that indicates
whether validation will be performed on the documents before they
are decomposed. The possible values are:
- 0
- Validation is not performed on the input documents before they
are decomposed.
- If a 0 value is passed and validation is not performed, it is
the user's responsibility to validate the documents before calling
the stored procedure. 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 0 is specified for this parameter, the decomposition results are
undefined. See the related reference at the
end of this topic for information about XML validation.
- 1
- Validation is performed on the input documents against DTDs or
XML schema documents previously registered with the XML schema repository.
The input XML documents are decomposed only if the validation is successful.
- commit_count
- An input argument of type INTEGER. The possible values are:
- 0
- No COMMIT is ever performed by the stored procedure.
- n, a positive integer
- After every n successful
document decompositions, a COMMIT is performed.
- allow_access
- An input argument of type INTEGER. The possible values are:
- 0
- The stored procedure will acquire an exclusive lock (X) on all
tables with mappings in the XML schema. Not all tables will necessarily
participate during decomposition of each document, but all target
tables will be locked to lower the possibility of deadlock during
a long unit of work.
- 1
- When acquiring locks, the stored procedure will wait and possibly
timeout.
- reserved
- The reserved argument is an input argument
reserved for future use. The value passed for this argument must be
NULL.
- reserved2
- The reserved2 argument is an input argument
reserved for future use. The value passed for this argument must be
NULL.
- continue_on_error
- An input argument of type INTEGER. The possible
values are:
- 0
- The stored procedure stops on the first document that cannot be
successfully decomposed. If an error occurs during the decomposition
of a document, changes to the database made during the decomposition
of the document are undone.
- 1
- The stored procedure does not stop on document-specific errors
and attempts to decompose all documents specified by query.
If an error occurs during the decomposition of a document, changes
to the database made during the decomposition of the document are
undone, and the stored procedure attempts to decompose the next document.
Information about any document that is not successfully decomposed
is written to result_report.
The stored procedure does not continue on fatal
errors and non-document specific errors regardless of the value of continue_on_error.
- total_docs
- An output argument of type INTEGER that specifies the total number
of input documents that the XDB_DECOMP_XML_FROM_QUERY stored procedure
attempted to decompose.
- num_docs_decomposed
- An output argument of type INTEGER that specifies the number of
documents that were successfully decomposed.
- result_report
- An output argument of type BLOB(100MB). A buffer
containing a UTF-8 XML document that lists the name of each input
file that was not successfully decomposed, along with a diagnostic
message. This report is generated only when there is at least one
XML document that could not be successfully decomposed.
The format of the XML document in result_report is
as follows:
<?xml version='1.0'?>
<xdb:errorReport xmlns:xdb="http://www.ibm.com/xmlns/prod/db2/xdb1">
<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 query. 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.
Output
The SQLCA structure indicates the
return status of the procedure after attempting to decompose the XML
documents. The procedure can return one of the following SQLCODE values:
- 0
- All documents specified by query were successfully
decomposed.
- 16278
- The decomposition of one or more documents failed.
The number of successful documents is given as the num_docs_decomposed output
parameter to the stored procedure. Individual error messages for each
failed document are recorded in result_report.
Further diagnostic details on each failure are logged in a db2diag log
file.
- negative integer
- No documents were decomposed. The SQLCODE indicates
the reason for the failure. Check the db2diag log
file for details of the failure.
Note
The stored procedure is declared with
these characteristics:
DYNAMIC RESULT SETS 0
NOT DETERMINISTIC
UNFENCED
THREADSAFE
MODIFIES SQL DATA
PARAMETERSTYLE SQL
CALLED ON NULL INPUT
NEW SAVEPOINT LEVEL
DBINFO
Example
The
following example assumes the table ABC.SALESTAB contains two columns
SALESDOC and DOCID. The column SALESDOC contains XML documents, and
the column DOCID contains the unique identifier for the XML documents
in SALESDOC. All the XML documents correspond to an XML schema registered
as ABC.SALES, and the schema has been annotated with decomposition
information and enabled for decomposition. The following example calls
the stored procedure to decompose the documents stored in SALESDOC
using the schema ABC.SALES:
XDB_DECOMP_XML_FROM_QUERY ('ABC', 'SALES',
'SELECT DOCID, SALESDOC FROM ABC.SALESTAB', 0, 0, 0,
NULL, NULL, 1, numInput, numDecomposed, errorreportBuf);