IBM Support

IC89017: XMLTABLE() RETURNING MULTIPLE XML NODES MAY RETURN SQL16003N

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error 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.
    

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');
    

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                                        *
    ****************************************************************
    

Problem conclusion

  • The fix will be included in db2_v97fp8
    

Temporary 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');
    

Comments

APAR Information

  • APAR number

    IC89017

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-12-11

  • Closed date

    2013-04-08

  • Last modified date

    2013-04-08

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC91723

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC89017

Modified date: 08 April 2013