My Oracle Support Banner

Wrong Results from UNION ALL Used Within WITH Section Using XMLTABLE (Doc ID 1589301.1)

Last updated on AUGUST 04, 2018

Applies to:

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


A UNION ALL of select statements from a WITH section, where the WITH section selects from XMLTABLE, is not returning the expected result.

Below is an example query:

xmldata as (
select t.izo, t.red_izo, t.zar_pln, t.misto, t.ulice, t.psc, t.rok
from (select xmltype('<?xml version="1.0"
kod="000010189" redizo="" nazev="Strední odborné uciliste strojírenské"
nazev_obce="Vsetín" ulice="Jasenická 1487" psc="75501"
rok_maturity="1999"/></ciselnik_stredni_skola.xml>') as object_value from
passing object_value
columns izo varchar2(9) path '@kod',
red_izo varchar2(9) path '@redizo',
zar_pln varchar2(255) path '@nazev',
misto varchar2(255) path '@nazev_obce',
ulice varchar2(255) path '@ulice',
psc number(5) path '@psc',
rok number(4) path '@rok_maturity') t
convdata as (
select izo, zar_pln, misto, ulice, psc, rok
from xmldata
union all
select red_izo as izo, zar_pln, misto, ulice, psc, rok
from xmldata
where red_izo is not null
select count(*)
from convdata;

which is returning 'no row selected' while each separated statement is returning rows.




To view full details, sign in with your My Oracle Support account.

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

In this Document

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