Scrambled Namespace Returned by a Query in Presence of NIL (Doc ID 1563243.1)

Last updated on FEBRUARY 08, 2017

Applies to:

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

Symptoms

Scrambled  namespace returned by the select statement

BASVI@S112W6 > WITH t AS (SELECT   lastrevision,d.whole_branch xml_document
FROM NOXSD__BSC_RE_1705147073D t,XmlTable('/' PASSING t.value COLUMNS
whole_branch XMLType PATH '/') d WHERE LASTREVISION='S'   and
K_INTERNAA='provaA1'   AND rownum < 10)
  2  SELECT lastrevision,XmlType.getClobVal(x.column_value) xml_clob FROM
t,XmlTable('for $i in /VALUE return
<value><xmlValue>{$i/xmlValue/BSC_Regression_REC1}</xmlValue></value>'
  3  PASSING t.xml_document) x;

L XML_CLOB
-
------------------------------------------------------------------------------
--
S <value><xmlValue><BSC_Regression_REC1>
  <CampoA>provaA1</CampoA>
  <CampoB>01/10/13</CampoB>
  <CampoC>1.5</CampoC>
  <CampoD>2017-01-13</CampoD>
  <CampoE>BSC1</CampoE>
  <CampoF>provaF1</CampoF>
  <CampoG>10</CampoG>
  <CampoH>5</CampoH>
  <CampoI xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" si:nil="true"/>
  <CampoJ xmlns:   ="http://www.w3.org/2001/XMLSchema-instance"   :nil="true"/>
<
  /BSC_Regression_REC1>
  </xmlValue></value>


The output is scrambling the definition of the namespace.
It also seems that invisible characters are added to the result as shown
below

<CampoI xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" si:nil="true"/>
<CampoJ xmlns:   ="http://www.w3.org/2001/XMLSchema-instance"   :nil="true"/>

The query work fine if it run the way as below from the same document

BASVI@S112W6 > SELECT   lastrevision,d.whole_branch xml_document FROM
NOXSD__BSC_RE_1705147073D t
,XmlTable('/' PASSING t.value COLUMNS whole_branch XMLType PATH '/') d
WHERE LASTREVISION='S'   and K_INTERNAA='provaA1'   AND rownum < 10;
  2    3
L
-
XML_DOCUMENT
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
--------------------
S
<VALUE>
  <references>
    <name>BSC_Regression_REC1_1</name>
    <owner>ntt_user</owner>
    <group/>
    <creationDate>2013-05-30T13:35:09</creationDate>
    <lastUpdateDate>2013-05-30T13:35:09</lastUpdateDate>
    <note/>
    <revision>1</revision>
  </references>
  <keys>
    <key name="Data1">2013-05-30</key>
    <key name="InternaA">provaA1</key>
  </keys>
  <xmlValue>
    <BSC_Regression_REC1>
      <CampoA>provaA1</CampoA>
      <CampoB>01/10/13</CampoB>
      <CampoC>1.5</CampoC>
      <CampoD>2017-01-13</CampoD>
      <CampoE>BSC1</CampoE>
      <CampoF>provaF1</CampoF>
      <CampoG>10</CampoG>
      <CampoH>5</CampoH>
      <CampoI xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
      <CampoJ xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
    </BSC_Regression_REC1>
  </xmlValue>
</VALUE>

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