Hello, TOM guys,

I have a problem/misunderstanding how xmlchaining works with missing tag and default case.

Database version: 12.1.0.2.0

Here is quick example of what my problem looks like:

with dummy as (
  select xmltype(
  '<Employees>
    <Employee>
        <firstname>A</firstname>
        <phones>
          <phone><value>test1</value></phone>
          <phone><value>test2</value></phone>
        </phones>
    </Employee>
    <Employee>
        <firstname>B</firstname>
        <phones>
          <phone></phone>
        </phones>
    </Employee>
    <Employee>
        <firstname>C</firstname>
    </Employee>
</Employees>'
  ) as data from dual
)
select t.name, p.phone
from dummy,
xmltable(
  'Employees/Employee' passing dummy.data
  columns
    name varchar2(20 char) path 'firstname'
    , phone xmltype path 'phones/phone'
) t,
xmltable(
  '/phone' passing t.phone
  columns
   phone varchar2(10 char) path 'value'
) p

Normally we expect to have Employee with <phones> tag and sequence of N <phone> tags inside, like in example A.
In this case eveything works fine.

Example B - is what i managed to do in order to make xmlchaining work like left join, but sadly its synthetic and if data missing, phones in this case, we have example C. There is no tag at all, so also there is no row in sqlresult, which looks like this.

A test1
A test2
B 

i was curios can i make such sythetic data by default if there is missing tag <phones>.
according to documentation https://docs.oracle.com/database/121/ADXDB/xdb_xquery.htm#ADXDB5097

The optional DEFAULT clause specifies the value to use when the PATH expression results in an empty sequence (or NULL). Its expr is an XQuery expression that is evaluated to produce the default value.

I thought that what i was lookin for and came up with update version:

with dummy as (
  select xmltype(
  '<Employees>
    <Employee>
        <firstname>A</firstname>
        <phones>
          <phone><value>test1</value></phone>
          <phone><value>test2</value></phone>
        </phones>
    </Employee>
    <Employee>
        <firstname>B</firstname>
        <phones><phone></phone></phones>
    </Employee>
    <Employee>
        <firstname>C</firstname>
    </Employee>
</Employees>'
  ) as data from dual
)
select t.name, p.phone
from dummy,
xmltable(
  'Employees/Employee' passing dummy.data
  columns
    name varchar2(20 char) path 'firstname'
    , phone xmltype path 'phones/phone'
    default xmltype(
    '<phones>
          <phone></phone>
        </phones>'
    )
) t,
xmltable(
  '/phone' passing t.phone
  columns
   phone varchar2(10 char) path 'value'
) p

But that gave me 0 rows output. Shouldnt it gave me result i was looking for? Can you help me?

with dummy as (
  select xmltype(
  '<Employees>
    <Employee>
        <firstname>A</firstname>
        <phones>
          <phone><value>test1</value></phone>
          <phone><value>test2</value></phone>
        </phones>
    </Employee>
    <Employee>
        <firstname>B</firstname>
        <phones><phone></phone></phones>
    </Employee>
    <Employee>
        <firstname>C</firstname>
    </Employee>
</Employees>'
  ) as data from dual
), phones as (
select /*+ no_merge */*
from dummy,
xmltable(
  'Employees/Employee' passing dummy.data
  columns
    name varchar2(20 char) path 'firstname'
    , phone xmltype path 'phones/phone'
   default xmltype('<phone><value></value></phone>')
) t
)
select t.phone, p.phone from phones t,
xmltable(
  '/phone' passing t.phone
  columns
   phone varchar2(100 char) path 'value'
) p;

PHONE   PHONE  
OPAQUE  test1  
OPAQUE  test2  
OPAQUE         
OPAQUE