Search This Blog

Showing posts with label xmltable xmltype. Show all posts
Showing posts with label xmltable xmltype. Show all posts

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