SELECT XML QUERY RETURNING 2 ROWS WHILE EXPECTING ONE AFTER UPGRADING TO 19.13 (Doc ID 2955292.1)

Last updated on MAY 16, 2024

Applies to:

Symptoms

Select statement returning correct result 1 row from 19.10

SQL> SELECT DISTINCT
c.NULLABLE
, c.IS_UNIQUE
, c.MAX_SIZE
, NVL(TRIM(REGEXP_REPLACE(x.KEY_NAME, '\s+', CHR(32))), c.KEY_NAME)
 2    3    4    5    6  AS KEY_NAME
, CASE c.DATA_TYPE WHEN 'Text' THEN x.VALUE ELSE
TRIM(REGEXP_REPLACE(x.VALUE, '\s+', CHR(32))) END AS "VALUE"
, c.TABLE_NAME
, c.FIELD_NAME
 7    8    9   10   11  , c.DATA_TYPE
, c.FORMAT
, c.REF_TABLE
, c.REF_FIELD
, c.CODE_FIELD
FROM XMLTABLE('*:nodeContent/*:info' PASSING XMLTYPE('<nodeContent
xmlns="http://.......">
<type>Building Group</type>
12   13   14   15   16  <info>
<keyName>BUILDING_GROUP_NAME</keyName>
<value>dddd</value>
17   18   19   20   21  </info>
<relationship>
<relationshipType>Owns</relationshipType>
<type>Building</type>
<action>Create</action>
<relatedFeatureIdentifier>
<context>IFH</context>
<id>56763501</id>
<version>20230207212130</version>
</relatedFeatureIdentifier>
22   23   24  </rel 25  ationship>
</nodeContent>')--gNodeContent
COLUMNS KEY_NAME VARCHAR2(4000) PATH '*:keyName'
, VALUE VARCHAR2(4000) PATH '*:value') x
26   27   28   29   30   31   32   33   34   35  FULL JOIN (SELECT * FROM
BGC_B2B_SERVICE_CONFIG WHERE NODE_TYPE = 'Building Group') c--gNodeType
ON c.KEY_NAME = TRIM(REGEXP_REPLACE(x.KEY_NAME, '\s+', CHR(32)))
WHERE x.KEY_NAME IS NOT NULL OR c.KEY_NAME IS NULL
OR c.NULLABLE = 0 AND null IS NULL;
36   37   38
 NULLABLE  IS_UNIQUE   MAX_SIZE
---------- ---------- ----------
KEY_NAME
------------------------------------------------------------------------------
--
VALUE
------------------------------------------------------------------------------
--
TABLE_NAME                     FIELD_NAME
------------------------------ ------------------------------
DATA_TYPE                      FORMAT
------------------------------ ------------------------------
REF_TABLE                      REF_FIELD
------------------------------ ------------------------------
CODE_FIELD
------------------------------
        0          1        255

However, starting from 19.13 till 19.15 its returning duplicate rows

Changes

 Upgrading to 19.13 and 19.15

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.