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