3 Replies Latest reply on Jul 14, 2019 7:29 PM by Subramanyam Yalla-Oracle

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

    Subramanyam Yalla-Oracle

      Hi,

       

      I wrote below code to extract data from xml data file.

       

      select   ata, mpditemnbr

          from  

              temp_tab , 

              xmltable( 

                  '/mpd/chapter/smits-section'

                  passing xmltype(file_data,1) 

                  columns 

                      FirstRecord xmltype path 'mpd-sys-maints' 

          ),

              xmltable( 

              'mpd-sys-maints' 

              passing FirstRecord 

              columns 

                      ata varchar2(100) path 'ata-header',

                      Record xmltype path 'mpd-sys-maint'),  

          xmltable( 

              'mpd-sys-maint' 

              passing Record 

              columns 

                  RecordTasknbr varchar2(100) path '@tasknbr', 

                  mpditemnbr    varchar2(30) path 'mpd-item-nbr'         

          )

       

      I am getting below error when executed this query:

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

      19279. 00000 -  "XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"

      *Cause:    The XQuery sequence passed in had more than one item.

      *Action:   Correct the XQuery expression to return a single item sequence.

       

      Please advise what mistake I am doing in the query.

       

      xml data file is given below:

       

      <?xml version="1.0" encoding="UTF-8"?>

      <!DOCTYPE mpd SYSTEM "mpboe04.dtd">

      <mpd spl="81205" model="777-200/300" oidate="01.JAN.1995" revdate="05.JAN.2019" tsn="019" docnbr="D622W001XMLSUP" lang="EN">

      <chapter key="JEFD20796A1B2319E206D4AF2E0F7F84" fragment="1" subfragment="0">

      <title>SYSTEM MAINTENANCE PROGRAM</title>

      <smits-section key="LF3D50E082B92E7E54E5161A661A3A99" fragment="1" subfragment="1">

      <title>SYSTEMS AND POWERPLANT MAINTENANCE PROGRAM</title>

      <mpd-sys-maints chapter-id="CH1">

      <ata-header>ATA 12: SERVICING</ata-header>

      <mpd-sys-maint tasknbr="12-002-01">

      <mpd-item-nbr>12-002-01</mpd-item-nbr>

      </mpd-sys-maint>

      <ata-header>ATA 20: STANDARD PRACTICES - AIRFRAME</ata-header>

      <mpd-sys-maint tasknbr="20-010-00">

      <mpd-item-nbr>20-010-00</mpd-item-nbr>

      </mpd-sys-maint>

      </mpd-sys-maints>

      </smits-section>

      </chapter>

      </mpd>

        • 1. Re: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
          mNem

          FYI, I did remove the reference to dtd in my test case. The input xml content column name is also different.

          • 2. Re: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
            mNem

            Assuming the xml content has a corresponding preceding <ata-header> element for each <mpd-sys-maint> element...

             

            with temp_tab (xml) as 
            (
            select '
            <mpd spl="81205" model="777-200/300" oidate="01.JAN.1995" revdate="05.JAN.2019" tsn="019" docnbr="D622W001XMLSUP"
                 lang="EN">
               <chapter key="JEFD20796A1B2319E206D4AF2E0F7F84" fragment="1" subfragment="0">
                  <title>SYSTEM MAINTENANCE PROGRAM</title>
                  <smits-section key="LF3D50E082B92E7E54E5161A661A3A99" fragment="1" subfragment="1">
                     <title>SYSTEMS AND POWERPLANT MAINTENANCE PROGRAM</title>
                     <mpd-sys-maints chapter-id="CH1">
                        <ata-header>ATA 12: SERVICING</ata-header>
                        <mpd-sys-maint tasknbr="12-002-01">
                           <mpd-item-nbr>12-002-01</mpd-item-nbr>
                        </mpd-sys-maint>
                        <ata-header>ATA 20: STANDARD PRACTICES - AIRFRAME</ata-header>
                        <mpd-sys-maint tasknbr="20-010-00">
                           <mpd-item-nbr>20-010-00</mpd-item-nbr>
                        </mpd-sys-maint>
                     </mpd-sys-maints>
                  </smits-section>
               </chapter>
            </mpd>' from dual
            )
            select ata, RecordTasknbr, mpditemnbr
            from   
            temp_tab ,  
            xmltable(  
                  '
                  /mpd/chapter/smits-section/mpd-sys-maints/mpd-sys-maint 
                  ' 
                  passing xmltype(xml)  
                  columns  
                      ata           varchar2(100) path './preceding-sibling::*[1][local-name() = "ata-header"]'
                      ,
                      RecordTasknbr varchar2(100) path '@tasknbr'
                      ,  
                      mpditemnbr    varchar2(30) path 'mpd-item-nbr'          
            ) x
            ;
            
            
            ATA                            RECORDTASKNBR   MPDITEMNBR                    
            ------------------------------ --------------- ------------------------------
            ATA 12: SERVICING              12-002-01       12-002-01                     
            ATA 20: STANDARD PRACTICES - A 20-010-00       20-010-00                     
            IRFRAME