with ex as
 (select xmltype('<Root>
<ExistingContracts>
<Contract>
<AgreementNumber value="XXX"/>
</Contract>
</ExistingContracts>
<TerminatedContracts>
<Contract>
<AgreementNumber value="YYY"/>
</Contract>
</TerminatedContracts>
</Root>') xml_ex
    from dual)
select *
  from ex,
       xmltable('/Root/ExistingContracts/Contract' passing ex.xml_ex
                columns agreementnumber varchar2(4000) path
                'AgreementNumber/@value') t_parse
union all
select *
  from ex,
       xmltable('/Root/TerminatedContracts/Contract' passing ex.xml_ex
                columns agreementnumber varchar2(4000) path
                'AgreementNumber/@value') t_parse

select *
  from ex, xmltable('/Root/ExistingContracts OR TerminatedContracts/Contract' --Вот здесь что то надо сделать. Не знаю возможно ли что то придумать
        passing ex.xml_ex columns agreementnumber varchar2(4000) path 'AgreementNumber/@value') t_parse