[SOLVED] Oracle XMLTABLE left outer join doesn't return results when XPath doesn't exist

Oracle XMLTABLE left outer join doesn't return results when XPath doesn't exist

The table tbl has a column xml_data of type XMLTYPE. Consider the following XML:

<root>
  <element>
    <id>1</id>
    <data>abc</data>
  </element>
  <element>
    <id>2</id>
    <data>def</data>
  </element>
</root>

I need a select statement that will return 1 row for each row in tbl, with 3 columns: id1, id2 & id3.

id1 will get the value of the "data" tag from the "element" node where the "id" is 1. id2 will get the value from the node where "id" is 2, etc.

In case there is no element for a certain id (like id3 in the XML above), NULL will be returned in that column.

So for the XML above I want to get

id1	id2	id3
abc	def	-

I tried:

SELECT id1.val, id2.val, id3.val
  FROM tbl t,
       XMLTable('/root/element[id=1]' 
                PASSING t.xml_data 
                COLUMNS val VARCHAR2(100) PATH 'data') id1,
       XMLTable('/root/element[id=2]' 
                PASSING t.xml_data 
                COLUMNS val VARCHAR2(100) PATH 'data') id2,
       XMLTable('/root/element[id=3]' 
                PASSING t.xml_data 
                COLUMNS val VARCHAR2(100) PATH 'data') id3;

But I get 0 rows because there is no '/root/element[id=3]'.

I tried adding (+):

XMLTable ('/root/element[id=3]'
          PASSING t.xml_data
          COLUMNS val VARCHAR2 (100) PATH 'data') (+) id3;

It didn't help (neither LEFT OUTER JOIN with ON 1=1).

I noticed that if the XMLQuery is valid but the "path" inside the xmltable doesn't exist, it DOES work and NULL is returned (even without the (+) ), but if the XQuery itself doesn't exist as in [id=3] it doesn't work.

SELECT id1.val, id2.val, id3.val
  FROM tbl t,
       XMLTable('/root/element[id=1]' 
                PASSING t.xml_data 
                COLUMNS val VARCHAR2(100) PATH 'data') id1,
       XMLTable('/root/element[id=2]' 
                PASSING t.xml_data 
                COLUMNS val VARCHAR2(100) PATH 'data') id2,
       XMLTable('/root/element[id=2]' 
                PASSING t.xml_data 
                COLUMNS val VARCHAR2(100) PATH 'doesnt-exist') id3;

Returns:

id1	id2	id3
abc	def	-

Solution

You don't need multiple calls to XMLTable, you can move the element selection into the column path clause, and have three such clauses:

select x.id1, x.id2, x.id3
from tbl t
cross join
  xmltable ('/root'
    passing t.xml_data
    columns id1 varchar2 (100) path 'element[id=1]/data',
       id2 varchar2 (100) path 'element[id=2]/data',
       id3 varchar2 (100) path 'element[id=3]/data'
) x;

ID1   ID2   ID3 
----- ----- -----
abc   def