<claimants>
    <claimant>
      <status_id>1</status_id>
      <status>Īpašnieks</status>
      <natural_person>
        <person_id>27075312958</person_id>
        <first_name>Ints</first_name>
        <surname>*</surname>
        <other_names/>
      </natural_person>
      <address>Dienvidu iela 7 k-2 - 89, Salaspils, Salaspils nov.</address>
      <shares>1/1</shares>
      <contacts>
        <phone>22113344</phone>
        <emails>
          <email>janis@test.com</email>
        </emails>
      </contacts>
    </claimant>
    <claimant>
      <status_id>3</status_id>
      <status>Trešā ieinteresētā puse</status>
      <legal_person>
        <unified_id>11111111111</unified_id>
        <full_name>A/S ManaBanka</full_name>
      </legal_person>
      <address>Balasta dambis 1B, Rīga</address>
      <shares></shares>
      <contacts>
        <phone>22331144</phone>
        <emails>
          <email>test1@test.com</email>
        </emails>
      </contacts>
    </claimant>
  </claimants>

if xmlclaim.extract('//claimants/claimant/contacts/emails/email['||to_char(i)||']/child::text()') is not null then
            emails:=xmlclaim.extract('//claimants/claimant/contacts/emails/email['||to_char(i)||']/child::text()').getstringval();

with data as (select xmltype(
'<claimants>
    <claimant>
      <status_id>1</status_id>
      <status>Īpašnieks</status>
      <natural_person>
        <person_id>27075312958</person_id>
        <first_name>Ints</first_name>
        <surname>*</surname>
        <other_names/>
      </natural_person>
      <address>Dienvidu iela 7 k-2 - 89, Salaspils, Salaspils nov.</address>
      <shares>1/1</shares>
      <contacts>
        <phone>22113344</phone>
        <emails>
          <email>janis@test.com</email>
        </emails>
      </contacts>
    </claimant>
    <claimant>
      <status_id>3</status_id>
      <status>Trešā ieinteresētā puse</status>
      <legal_person>
        <unified_id>11111111111</unified_id>
        <full_name>A/S ManaBanka</full_name>
      </legal_person>
      <address>Balasta dambis 1B, Rīga</address>
      <shares></shares>
      <contacts>
        <phone>22331144</phone>
        <emails>
          <email>test1@test.com</email>
        </emails>
      </contacts>
    </claimant>
  </claimants>'
, null, 1, 1) xmlclaim from dual
) select "status_id", "status", email, person_id
from data, XMLTABLE(
  '$X//claimant' PASSING xmlclaim AS X 
  columns 
  "status_id" number,
  "status",
  EMAIL varchar2(63) PATH 'contacts/emails/email',
  PERSON_ID number PATH '//person_id',
  unified_id number path '//unified_id'
);

status_id     status                   EMAIL        PERSON_ID 
------------  ------------------------ ------------ -----------
1             Īpašnieks                janis@test.c 27075312958
3             Trešā ieinteresētā p     test1@test.c 

if xmlclaim.extract('(//emails/email)['||to_char(i)||']/child::text()') is not null then
   emails:=xmlclaim.extract('(//emails/email)['||to_char(i)||']/child::text()').getstringval();