IBM Support

IC91723: 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                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 10.1 Fix Pack 3.                      *
    ****************************************************************
    

Problem conclusion

  • Upgrade to DB2 version 10.1 Fix Pack 3.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC91723

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-04-22

  • Closed date

    2013-10-02

  • Last modified date

    2013-10-02

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

    IC89017

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSN

       UP

  • RA10 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 10.1

Reference #: IC91723

Modified date: 02 October 2013