Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
I want to apply left join on Employee table t with xml table function x

Hi All,
There is a case when EmployeeDetails doesn't have any data in the column and then in that case
it doesn't give results for t.object_id and employee code
SELECT t.object_id,NVL(t.xml_data.extract('//EMPLOYEE-CODE/text()').getStringVal(),' ') AS "Employee Code",x.EmpIndex,NVL(x.Einstance,' ') as Empolyee_Instance
FROM Employee t,
XMLTable('//EMPLOYEEDETAILS'
passing t.xml_data
columns
EmpIndex varchar2(10) path 'INDEX/text()',
Einstance varchar2(10) path 'INSTANCE-ID/text()'
) x
left join
XMLTable('//EDATA'
passing t.xml_data
columns
Ecode varchar2(10) path 'ECODE/text()') y
on x.pindex=y.phyindex
where t.object_type=5100 order by t.time_stamp desc
;
Please help with the above query.
I am able to apply left join between employee and xmltable x but as soon as try to join xmltable y
i dont get the results of table t
Answers
-
Change
on x.pindex=y.phyindex
to
on 1=1
Your join is already being done via the PASSING clause.
Also you don't need all those /text() in the XMLTable entries, avoid using // if possible for performance reasons and get rid of t.xml_data.extract in your SELECT clause. That needs to be handled down in the FROM. Odds are your whole query can be re-written in a better manner but without knowing what your XML looks like, that is just a guess.