|
Problem IC89017
|
Status: Closed |
XMLTABLE() RETURNING MULTIPLE XML NODES MAY RETURN SQL16003N |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All platforms *
****************************************************************
* PROBLEM DESCRIPTION: *
* 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. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to db2_v97fp8 *
**************************************************************** |
| Local Fix: |
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'); |
| available fix packs: |
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
The fix will be included in db2_v97fp8 |
| Workaround |
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'); |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC91723
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 11.12.2012
08.04.2013
08.04.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.8
|
 |
