Oracle XMLTABLE left outer join doesn't return

问题:

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
   --------------------
1  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
   --------------------
1  abc    def    -

回答1:

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        

回答2:

Hope this helps

WITH TEMP AS(
SELECT ID,DATA_VALUE
  FROM XMLTABLE('./root/element' PASSING XMLTYPE('<root>
  <element>
    <id>1</id>
    <data>abc</data>
  </element>
  <element>
    <id>2</id>
    <data>def</data>
  </element>
</root>')
COLUMNS ID NUMBER PATH './id',
        DATA_VALUE VARCHAR2(240) PATH './data'))
SELECT (SELECT DATA_VALUE FROM TEMP WHERE ID = 1) ID1, 
      (SELECT DATA_VALUE FROM TEMP WHERE ID = 2) ID2,
      (SELECT DATA_VALUE FROM TEMP WHERE ID = 3) ID3
  FROM DUAL;