Forum Stats

  • 3,769,306 Users
  • 2,252,946 Discussions


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

User_KGXOS Member Posts: 1 Green Ribbon
edited Oct 9, 2021 4:42PM in XML DB

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,


    passing t.xml_data 


    EmpIndex varchar2(10) path 'INDEX/text()',

    Einstance varchar2(10) path 'INSTANCE-ID/text()'

   ) x

left join


   passing t.xml_data 


   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)
    Jason_(A_Non) Member Posts: 2,044 Silver Trophy


    on x.pindex=y.phyindex


    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.