Proper parsing of XML file containing multi-item sequence using PL/SQL

PROBLEM:

If we have some XML data files with some recurring elements in them and try to parse it using XMLTABLE function, result can be following error:

ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence


For example XML data are  in file_name.xml:

<?xml version='1.0' encoding='windows-1250'?>
<PersonData>
 <KomitData>
   <BankKom>1</BankKom>
   <Komitent>1111111</Komitent>
   <TaxNo>01010101</TaxNo>
   <Docs>
     <Doc><DokTip>1</DokTip><DokSt>2723933</DokSt> </Doc>
     <Doc><DokTip>2</DokTip><DokSt>901148346</DokSt></Doc>
   </Docs>
   <Addrs>
    <Addr><NasTip>1</NasTip><NasNsl>ABC</NasNsl></Addr>
    </Addr>
   </Addrs>
 </KomitData>
</PersonData>


and query is:

select *
        FROM xmltable('/PersonData/KomitData' passing
                      xmltype(bfilename('FOLDER_NAME', 'file_name.xml:'),
                              nls_charset_id('windows-1250')) columns
                      BankKom NUMBER path 'BankKom'
                      Komitent  NUMBER path 'Komitent',
                      TaxNo      NUMBER path 'TaxNo',
                      DokType NUMBER path 'Docs/Doc/DokTip',
                      DokNo NUMBER path 'Docs/Doc/DokSt,
                      NasTip NUMBER path 'Addrs/Addr/NasTip',
                      NasNslVRACHAR2 path 'Addrs/Addr/NasNsl
)

This results in above error (ORA-19279) beacause  XQuery sequence passed in had more than one item.


SOLUTION:

Modify above query to take into account multiple sequnces:

 select *
        FROM xmltable('/PersonData/KomitData' passing
                      xmltype(bfilename('FOLDER_NAME', 'file_name.xml:'),
                              nls_charset_id('windows-1250')) columns
                      BankKom NUMBER path 'BankKom'
                      Komitent  NUMBER path 'Komitent',
                      TaxNo      NUMBER path 'TaxNo',
                      Dokuments xmltype path 'Docs',
                      NasTip NUMBER path 'Addrs/Addr/NasTip',
                      NasNslVRACHAR2 path 'Addrs/Addr/NasNsl
                       )  tbl,
                    xmltable('/Docs/Doc' passing tbl.Dokuments columns
                      DokType NUMBER path 'DokTip',
                      DokNo NUMBER path 'DokSt) tblDocs


run this query and enjoy in results.