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
    Subramanyam Yalla-Oracle Jul 12, 2019 7:52 PM

      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>

      • 1944 Views
      • Tags:
        • 1. Re: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
          mNem
          mNem Jul 13, 2019 3:44 AM (in response to Subramanyam Yalla-Oracle)

          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
            mNem Jul 13, 2019 7:49 AM (in response to Subramanyam Yalla-Oracle)

            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