DB2 FOR LUW / DB2FORLUW / A10 - DB2

An SQL16003N may be unexpectedly returned from an SQL/XQuery 
statement that 
satisfies all the conditions below: 
 
1. The statement uses the function XMLTABLE(). 
2. The XMLTable() containing XPath or XQuery expressions that 
returns multiple XML nodes. 
3. Each XML node of the result is then passed into SQL value and 
used in a comparison operator. 
 
Below is an example of the SQL/XQuery statement that illustrates 
the problem. 
 
CREATE TABLE XT(ID INT, DOC XML); 
INSERT INTO XT VALUES 
(1, '<Persons><Person ID="1">MR.A</Person><Person 
ID="2">MR.B</Person></Persons>'); 
 
SELECT P.ID1 
FROM XT AS R, 
          XMLTABLE('$D/Persons/Person/@ID' PASSING R.DOC AS "D" 
COLUMNS ID1 VARCHAR(20) PATH '.') AS P, 
         (SELECT R.ID 
          FROM XT AS R, 
                    XMLTABLE('$D/Persons/Person/@ID' PASSING 
R.DOC AS "D" COLUMNS ID2 VARCHAR(20) PATH '.') AS Q 
          WHERE ID2='1'); 
 
The above statement lists all the persons' IDs if there exists a 
person of ID equals to 1 in the XML document.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 10.1 Fix Pack 3.                      * 
****************************************************************

Use the fn:string() to convert the XML nodes that are passed to 
SQL value used in a comparison operator. From the above 
statement, return column "D" as string as in the modified 
statement below. 
 
SELECT P.ID1 
FROM XT AS R, 
          XMLTABLE('$D/Persons/Person/@ID' PASSING R.DOC AS "D" 
COLUMNS ID1 VARCHAR(20) PATH '.') AS P, 
         (SELECT R.ID 
          FROM XT AS R, 
                    XMLTABLE('$D/Persons/Person/@ID' PASSING 
R.DOC AS "D" COLUMNS ID2 VARCHAR(20) PATH 'string(.)') AS Q 
          WHERE ID2='1');

DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

Upgrade to DB2 version 10.1 Fix Pack 3.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

22.04.2013
02.10.2013
02.10.2013