create table t(id int,data xmltype);
insert into t values (1,xmltype('
<Results>
<Envelope>
<Summary ResultCount="3">
<Result Item="1">
<Detail Item="1">1.1</Detail>
</Result>
<Result Item="2">
<Detail Item="1">2.1</Detail>
<Detail Item="2">2.2</Detail>
<Detail Item="3">2.3</Detail>
</Result>
<Result Item="3">
<Detail Item="1">3.1</Detail>
<Detail Item="2">3.2</Detail>
</Result>
</Summary>
</Envelope>
</Results>
'))
insert into t values (2,xmltype('
<Results>
<Envelope>
<Summary>No search results</Summary>
</Envelope>
</Results>
'))
Query
select
id,
result_no,detail_no,detail_text,no_results
from t,
XMLTable('//Summary' passing t.data
COLUMNS no_results PATH 'text()'
) xtab1
, XMLTable('//Result' passing t.data
COLUMNS result_no PATH '@Item',detail xmltype path 'Detail'
) xtab2
,
XMLTable('/Detail' passing xtab2.detail
COLUMNS
detail_no PATH '@Item',
detail_text PATH 'text()'
) xtab3
The query works fine for id=1 but when there are no search results (id=2), it drops the entire row instead of showing the text() content of the Summary node.
How can the query be modified to show all records in the table? If there are serach results, the result_no/detail_no/detail_text columns will be populated, otherwise the no_results column will be populated.
Help? Thanks