Forum Stats

  • 3,769,306 Users
  • 2,252,946 Discussions
  • 7,874,982 Comments

Discussions

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

User_KGXOS
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,

    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

Tagged:

Answers

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,044 Silver Trophy

    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.