XML Query XMLTable Returns Wrong Results (Doc ID 1475800.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.2 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Symptoms

 Select statement using XMLTable returns 0 rows in 11.2.0.3 (it also reproduces in 11.2.0.2)

The same select returns 112,069 rows in 11.2.0.1

SELECT paylHdr.DUNS_NBR,EmpFig.EMP_FIG_SCOP_CD,EmpFig.EMP_MIN_QTY,EmpFig.EMP_QTY,EmpFig.EMP_FIG_SCOP_RELB_CD,EmpFig.END_INDC
    FROM
      "RPRTOWNER"."GDM_RAW",
      XMLTable( XMLNAMESPACES( DEFAULT 'http://www.dnb.com/GSRL/Vers8',
      'http://www.dnb.com/GSRL8/InventoryData/Header'  AS "plh",
      'http://www.dnb.com/GSRL8/InventoryData/Bar'  AS "bar",
      'http://www.dnb.com/InventoryData' AS "invtdata" ),
      ' //GSRL/GSRLMSGSRQV1/SUBJUPDTRNRQ/SUBJUPDRQ/invtdata:INVT_DATA'
      PASSING "RPRTOWNER"."GDM_RAW".invt_data COLUMNS PAYL_HDR XMLTYPE PATH 'plh:PAYL_HDR',EMP_FIG XMLTYPE PATH 'bar:EMP_FIG') INVTDATA,
      XMLTable( XMLNAMESPACES( DEFAULT 'http://www.dnb.com/GSRL/Vers8',
      'http://www.dnb.com/GSRL8/InventoryData/Header'  AS "plh",
      'http://www.dnb.com/InventoryData' AS "invtdata" ),
      ' //plh:PAYL_HDR'
      PASSING INVTDATA.PAYL_HDR COLUMNS DUNS_NBR varchar2(15) PATH 'plh:DUNS_NBR'
      ) paylHdr,
      XMLTable( XMLNAMESPACES( DEFAULT 'http://www.dnb.com/GSRL/Vers8',
      'http://www.dnb.com/GSRL8/InventoryData/Bar'  AS "bar"
       ),
       '//bar:NBR_EMP_ENTR'      
      PASSING INVTDATA.EMP_FIG COLUMNS EMP_QTY number(10) PATH 'bar:EMP_QTY',
      EMP_FIG_SCOP_CD varchar2(5) PATH 'bar:EMP_FIG_SCOP_CD',
      EMP_MIN_QTY number(10) PATH 'bar:EMP_MIN_QTY',
      EMP_FIG_SCOP_RELB_CD varchar2(7) PATH 'bar:EMP_FIG_SCOP_RELB_CD',
      END_INDC  number(4) PATH 'bar:END_INDC'
      ) EmpFig;

0 rows selected

In 11.2.0.1 this Select returns 112,069 rows

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms