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