I want to apply left join on Employee table t with xml table function x

User_KGXOS

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

Jason_(A_Non)

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.