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