Oracle XML Parse to Table Multiple Sub Elements

Hello I have an xml string I want to parse it to table. Problem is that when I have multiple sub categories in xml it throws exceptions.

[Error] Execution (27: 35): ORA-19279: XPTY0004 – XQuery dynamic type mismatch: expected singleton sequence – got multi-item sequence

MasaPersonelUrunleri, MasaPersonel can be more than one . When I write single every of them query below works but when I put one more element it throws error. How can solve the problem ?

select *  
FROM XMLTABLE('/Masa'  
         PASSING   
            xmltype('
               <Masa>
                    <ID>0</ID>
                    <SICIL>60950</SICIL>
                    <TARIH>2020-03-20T17:00:03</TARIH>
                    <IS_KERZZ>0</IS_KERZZ>
                    <MASA>
                        <MasaPersonel>
                            <SICIL>60950</SICIL>
                            <AD_SOYAD>Test User</AD_SOYAD>
                            <TOTAL>0</TOTAL>
                            <MASA_ID>0</MASA_ID>
                            <RESERVATION_ID>0</RESERVATION_ID>
                            <ID>0</ID>
                            <URUNLER>
                                <MasaPersonelUrunleri>
                                    <ID>0</ID>
                                    <ADET>1</ADET>
                                    <BIRIM_FIYAT>20</BIRIM_FIYAT>
                                    <SICIL>60950</SICIL>
                                    <URUN_KOD>URN284</URUN_KOD>
                                    <URUN_AD>IZGARA PİLİÇ</URUN_AD>
                                </MasaPersonelUrunleri>
                                <MasaPersonelUrunleri>
                                    <ID>0</ID>
                                    <ADET>1</ADET>
                                    <BIRIM_FIYAT>25</BIRIM_FIYAT>
                                    <SICIL>60950</SICIL>
                                    <URUN_KOD>URN285</URUN_KOD>
                                    <URUN_AD>TAVUK PİLİÇ</URUN_AD>
                                </MasaPersonelUrunleri>
                            </URUNLER>
                        </MasaPersonel>
                    </MASA>
                </Masa>
            ')
         COLUMNS  
            --describe columns and path to them:  
            SICIL  varchar2(20)    PATH './SICIL',  
            TARIH varchar2(20)     PATH './TARIH',
            PERSONEL  varchar2(20) PATH './MASA/MasaPersonel/SICIL',
            URUN_KODU varchar2(20) PATH './MASA/MasaPersonel/URUNLER/MasaPersonelUrunleri/URUN_KOD',
            URUN_ADI varchar2(20)  PATH './MASA/MasaPersonel/URUNLER/MasaPersonelUrunleri/URUN_AD',
            URUN_ADETI number      PATH './MASA/MasaPersonel/URUNLER/MasaPersonelUrunleri/ADET',
            URUN_FIYATI number     PATH './MASA/MasaPersonel/URUNLER/MasaPersonelUrunleri/BIRIM_FIYAT'
     ) xmlt  
;  

Answer

You could use chained XMLTable calls, but in this case you can make your XPath go down to the multi-element level:

select *  
FROM XMLTABLE('/Masa/MASA/MasaPersonel/URUNLER/MasaPersonelUrunleri'  

and then adjust the column paths to walk back up the tree:

         COLUMNS  
            --describe columns and path to them:  
            SICIL  varchar2(20)    PATH './../../../../SICIL',  
            TARIH varchar2(20)     PATH './../../../../TARIH',
            PERSONEL  varchar2(20) PATH './../../SICIL',
            URUN_KODU varchar2(20) PATH 'URUN_KOD',
            URUN_ADI varchar2(20)  PATH 'URUN_AD',
            URUN_ADETI number      PATH 'ADET',
            URUN_FIYATI number     PATH 'BIRIM_FIYAT'
     ) xmlt  
;  

which gets:

SICIL                TARIH                PERSONEL             URUN_KODU            URUN_ADI             URUN_ADETI URUN_FIYATI
-------------------- -------------------- -------------------- -------------------- -------------------- ---------- -----------
60950                2020-03-20T17:00:03  60950                URN284               IZGARA PİLİÇ                  1          20
60950                2020-03-20T17:00:03  60950                URN285               TAVUK PİLİÇ                   1          25

db<>fiddle

---

The chained XMLTable approach would be something like:

select xml1.SICIL, xml1.TARIH, xml1.PERSONEL, xml2.URUN_KODU, xml2.URUN_ADI, xml2.URUN_ADETI, xml2.URUN_FIYATI
FROM XMLTABLE('/Masa'  
...
         COLUMNS  
            --describe columns and path to them:  
            SICIL  varchar2(20)    PATH './SICIL',  
            TARIH varchar2(20)     PATH './TARIH',
            PERSONEL  varchar2(20) PATH './MASA/MasaPersonel/SICIL',
            URUNLERI xmltype       PATH './MASA/MasaPersonel/URUNLER/MasaPersonelUrunleri'
    ) xml1
CROSS JOIN XMLTABLE ('/MasaPersonelUrunleri'
         PASSING xml1.URUNLERI
         COLUMNS  
            URUN_KODU varchar2(20) PATH './URUN_KOD',
            URUN_ADI varchar2(20)  PATH './URUN_AD',
            URUN_ADETI number      PATH './ADET',
            URUN_FIYATI number     PATH './BIRIM_FIYAT'
     ) xml2  
;  

where each XMLTable is producing some of the columns; the multi-item sequence is the link between them, extracted from the first XMLTable and passed into the second.

db<>fiddle

PERSONEL varchar2(20) PATH './MASA/MasaPersonel/SICIL' causes problem when you have more than one MASA/MasaPersonel nodes

That isn’t shown in the sample in the question, but you can handle it with another chained XMLTable:

select xml1.SICIL, xml1.TARIH, xml2.PERSONEL, xml3.URUN_KODU, xml3.URUN_ADI, xml3.URUN_ADETI, xml3.URUN_FIYATI
FROM XMLTABLE('/Masa'  
...
         COLUMNS  
            --describe columns and path to them:  
            SICIL  varchar2(20)    PATH './SICIL',  
            TARIH varchar2(20)     PATH './TARIH',
            MASAPERSONEL xmltype   PATH './MASA/MasaPersonel'
    ) xml1
CROSS JOIN XMLTABLE ('/MasaPersonel'
         PASSING xml1.MASAPERSONEL
         COLUMNS  
            PERSONEL varchar2(20)  PATH './SICIL',
            URUNLERI xmltype       PATH './URUNLER/MasaPersonelUrunleri'
     ) xml2  
CROSS JOIN XMLTABLE ('/MasaPersonelUrunleri'
         PASSING xml2.URUNLERI
         COLUMNS  
            URUN_KODU varchar2(20) PATH './URUN_KOD',
            URUN_ADI varchar2(20)  PATH './URUN_AD',
            URUN_ADETI number      PATH './ADET',
            URUN_FIYATI number     PATH './BIRIM_FIYAT'
     ) xml3
;  

db<>fiddle