Search This Blog

Tuesday, October 4, 2011

How to extract more that one records from xml file

Following is the example where vacancy has two recruiters.

select x.*,x1.*
from hr_api_transactions t,
     xmltable('//PerAllVacanciesEORow'
              passing xmltype(transaction_document)
              columns requisition_id  number        path 'RequisitionId',
                      organization_id number        path 'OrganizationId',
                      name            varchar2(100) path 'Name',
                      creation_date   varchar2(30)  path 'CreationDate'                     
             ) x,
      xmltable('//IrcRecTeamMembersEORow'
              passing xmltype(transaction_document)
              columns PersonId  varchar2(10)        path 'PersonId'                                        
             ) x1
where t.transaction_ref_table = 'PER_ALL_VACANCIES'
and t.transaction_ref_id = 146

No comments:

Post a Comment