XPath不存在时，Oracle XMLTABLE左外部联接不会返回结果 [英] Oracle XMLTABLE left outer join doesn't return results when XPath doesn't exist

问题描述

表tbl的列xml_data为XMLTYPE类型. 考虑以下XML:

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>

我需要一条select语句，该语句将为tbl中的每一行返回1行，其中包含3列:id1，id2和id3.

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

id1将从"id"为1的"element"节点获取"data"标记的值.id2将从"id"为2的节点获取值.以此类推.

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.

如果没有用于特定ID的元素(如上述XML中的id3)，则该列将返回NULL.

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

对于上面的XML，我想得到

So for the XML above I want to get

   id1    id2    id3
   --------------------
1  abc    def    -

我尝试过:

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;

但是我得到0行，因为没有'/root/element [id = 3]'.

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

我尝试添加(+):

xmltable ('/root/element[id=3]'
        passing t.xml_data
        columns val varchar2 (100) path 'data') (+) id3;

它没有帮助(都没有将ON 1 = 1的LEFT OUTER JOIN连接起来).

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

我注意到，如果XMLQuery有效，但xmltable中的路径"不存在，则它可以工作并且返回NULL(即使没有(+))，但是如果XQuery本身不存在，因为[id = 3]中不起作用.

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;

返回:

   id1    id2    id3
   --------------------
1  abc    def    -

推荐答案

您不需要多次调用XMLTable，可以将元素选择移到列path子句中，并具有三个这样的子句:

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        

这篇关于XPath不存在时，Oracle XMLTABLE左外部联接不会返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！