4 Replies Latest reply: Jul 15, 2013 4:21 AM by Ken R RSS

    XQuery Dynamic Type mismatch (ORA-19279)

    Ken R

      Hello,

       

      I'm getting "ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence" error with the below XML block. Query is working fine when there are no repeating XML element blocks (Address, Income & Telephone). Also, does the query look good from a performance standpoint ?

       

      with Test as (select xmltype

      ('<SampleRequest xmlns:ns0="http://abc.org/SampleRequest/one" xmlns="http://abc.org/SampleRequest/one">

         <ns0:Address>

            <ns0:AddressType>

               <CurrAdrInd xmlns="">Y</CurrAdrInd>

               <OutOfAreaInd xmlns="">N</OutOfAreaInd>

               <YearDur xmlns="">15</YearDur>

               <MthDur xmlns="">0</MthDur>

            </ns0:AddressType>

            <AdrLine1 xmlns="">Line 1</AdrLine1>

            <AdrLine2 xmlns="">Line 2</AdrLine2>

            <AdrLine3 xmlns="">Line 3</AdrLine3>

            <TownName xmlns="">Town Name</TownName>

            <PostCd xmlns="">123456</PostCd>

         </ns0:Address>

         <ns0:Address>

            <ns0:AddressType>

               <CurrAdrInd xmlns="">N</CurrAdrInd>

               <OutOfAreaInd xmlns="">Y</OutOfAreaInd>

               <YearDur xmlns="">5</YearDur>

               <MthDur xmlns="">0</MthDur>

            </ns0:AddressType>

            <AdrLine1 xmlns="">Line 1</AdrLine1>

            <AdrLine2 xmlns="">Line 2</AdrLine2>

            <AdrLine3 xmlns="">Line 3</AdrLine3>

            <TownName xmlns="">Town Name</TownName>

            <PostCd xmlns="">987654</PostCd>

         </ns0:Address>

         <ns0:Customer>

            <TitleCd xmlns="">Mr</TitleCd>

            <FrstName xmlns="">Customer 1</FrstName>

            <MidName xmlns=""/>

            <LastName xmlns="">LAST</LastName>

            <BrthDt xmlns="">1979-06-21</BrthDt>

         </ns0:Customer>

         <ns0:Income>

            <IncmTypeCd xmlns="">Gross</IncmTypeCd>

            <IncmAmt xmlns="">2700</IncmAmt>

         </ns0:Income>

         <ns0:Income>

            <IncmTypeCd xmlns="">Other</IncmTypeCd>

            <IncmAmt xmlns="">1500</IncmAmt>

         </ns0:Income>

         <ns0:Telephone>

            <PhnNumTypeCd xmlns="">Home</PhnNumTypeCd>

            <PhnAreaCd xmlns="">123</PhnAreaCd>

            <PhnNum xmlns="">123467</PhnNum>

         </ns0:Telephone>

         <ns0:Telephone>

            <PhnNumTypeCd xmlns="">Work</PhnNumTypeCd>

            <PhnAreaCd xmlns="">987</PhnAreaCd>

            <PhnNum xmlns="">9876543</PhnNum>

         </ns0:Telephone>

      </SampleRequest>

      ') clob_xml from dual)

      select x.* from test,

      xmltable(xmlnamespaces('http://abc.org/SampleRequest/one' as "ns0"),

      'for $i in /ns0:SampleRequest

      return $i' passing clob_xml

      columns

      ttl_cd varchar2(10) path '//ns0:Customer/TitleCd',

      frst_nm varchar2(32) path '//ns0:Customer/FrstName',

      mid_nm varchar2(1) path '//ns0:Customer/MidName',

      last_nm varchar2(32) path '//ns0:Customer/LastName',

      brth_dt date path '//ns0:Customer/BrthDt',

      curr_adr_ind varchar2(1) path '//ns0:Address/ns0:AddressType/CurrAdrInd',

      out_of_area_ind varchar2(1) path '//ns0:Address/ns0:AddressType/OutOfAreaInd',

      tm_at_adr_year_dur number(3) path '//ns0:Address/ns0:AddressType/YearDur',

      tm_at_adr_mth_dur number(2) path '//ns0:Address/ns0:AddressType/MthDur',

      flat_num varchar2(32) path '//ns0:Address/AdrLine1',

      hse_num varchar2(32) path '//ns0:Address/AdrLine2',

      hse_nm varchar2(32) path '//ns0:Address/AdrLine3',

      town_nm varchar2(32) path '//ns0:Address/TownName',

      post_cd varchar2(8) path '//ns0:Address/PostCd',

      incm_type_cd varchar2(1) path '//ns0:Income/IncmTypeCd',

      incm_amt number(10) path '//ns0:Income/IncmAmt',

      phn_num_type_cd varchar2(2) path '//ns0:Telephone/PhnNumTypeCd',

      phn_num_area_cd varchar2(5) path '//ns0:Telephone/PhnAreaCd',

      phn_num varchar2(12) path '//ns0:Telephone/PhnNum'

      ) x;

       

      Regards,

      Chandra KenR

        • 1. Re: XQuery Dynamic Type mismatch (ORA-19279)
          odie_63
          I'm getting "ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence" error with the below XML block. Query is working fine when there are no repeating XML element blocks (Address, Income & Telephone).

          The error makes sense, doesn't it ?

          You're trying to project a collection of items into a single column.

           

          Are the cardinalities of those collections bounded to relatively low numbers?

           

          Can you show the exact output you require ?

          • 2. Re: XQuery Dynamic Type mismatch (ORA-19279)
            Ken R

            Hello Odie,

             

            In my example, Address can be of two types - Current & Previous which can be determined using CurrAdrInd element. Likewise, with Income & Telephone tags which are determined based on IncmTypeCd and PhnNumTypeCd respectively.

             

            My initial idea was to pivot the result set once I get the duplicate rows from XQuery. But, if there is a way to project the columns based on values using XQuery, that would be great, so I don't have to pivot.

             

            I would like to see Income elements appear as additional columns based on their codes (same for address & telephone as well). For eg.

             

            grs_incm_type_cd     grs_incm_amt     oth_incm_type_cd     oth_incm_amt

            ----------------     ------------     ----------------     ------------

            G                    2700             O                    1500

             

            And the cardinality for the above will be either 2 or 3, not more than that.

            Thanks in advance.

            • 3. Re: XQuery Dynamic Type mismatch (ORA-19279)
              odie_63

              Thanks, it's much clearer this way.

               

              For example, to retrieve the income-related information :

              SQL> select x.*

                2  from tmp_xml t

                3     , xmltable(

                4         xmlnamespaces('http://abc.org/SampleRequest/one' as "ns0")

                5       , '/ns0:SampleRequest'

                6         passing t.object_value

                7         columns

                8           ttl_cd       varchar2(10) path 'ns0:Customer/TitleCd'

                9         , frst_nm      varchar2(32) path 'ns0:Customer/FrstName'

              10         , mid_nm       varchar2(1)  path 'ns0:Customer/MidName'

              11         , last_nm      varchar2(32) path 'ns0:Customer/LastName'

              12         , brth_dt      date         path 'ns0:Customer/BrthDt'

              13         , grs_incm_amt number(10)   path 'ns0:Income[IncmTypeCd="Gross"]/IncmAmt'

              14         , oth_incm_amt number(10)   path 'ns0:Income[IncmTypeCd="Other"]/IncmAmt'

              15       ) x

              16  ;

               

              TTL_CD     FRST_NM                          MID_NM LAST_NM                          BRTH_DT     GRS_INCM_AMT OTH_INCM_AMT

              ---------- -------------------------------- ------ -------------------------------- ----------- ------------ ------------

              Mr         Customer 1                              LAST                             21/06/1979          2700         1500

               

               

              About your question on performance :

              Yes, there are general guidelines to follow in order to get best performance out of XQuery execution.

              Those are both storage-dependent and storage-independent, with a larger importance of the former.

               

              For instance, in the test case above, I first stored the XML document in a binary XMLType table (TMP_XML) : this is a storage-dependent optimization.

              I also removed all the descendant axes (//) from the PATH expressions : those were not necessary and should be avoided if the exact location of the target node is known, that's typically a storage-independent optimization.

               

              You'll find everything you need to know in details here :

              http://www.oracle.com/technetwork/database-features/xmldb/overview/xmldb-bpwp-12cr1-1964809.pdf

              • 4. Re: XQuery Dynamic Type mismatch (ORA-19279)
                Ken R

                Thanks a lot for the link. Much appreciated.