CODEWITHSUNDEEP
xmloracle-database
newinIT
newinIT

Reputation: 15

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - different case

I have some problem trying to select some fields from an XML file in Oracle.

I'm getting the error below:

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

The XML structure is like:

<REC r_id_disclaimer="yyyyyyy">
   <UID>xxxxxxx</UID>
   <static_data>
   <keywords count="4">
       <keyword>Brain-</keyword>
       <keyword>Depres</keyword>
       <keyword>Acute</keyword>
       <keyword>Chine</keyword>
   </keywords>

The query I'm using is the below:

select RecUid.CD_UID
     , Abst2.*
from testtable2 t
cross join  xmltable(xmlnamespaces(default 'http://scientific.thomsonreuters.com/schema/wok5.4/public/FullRecord'),
 'records/REC'
  passing t.xml_file 
  columns     CD_UID varchar2(200) path 'UID',
              --names xmltype path 'static_data/summary'
              Abstract xmltype path 'static_data/fullrecord_metadata'
            ) RecUid


cross join xmltable(xmlnamespaces(default 'http://scientific.thomsonreuters.com/schema/wok5.4/public/FullRecord'),
 'keywords/keywords'
  passing RecUid.Abstract

  columns     keyword varchar2(200) path 'keyword'
            ) as Abst2
    ;

I've read all the others topics related to the same error, but I cannot find a solution for my case. I've tried with 

columns     keyword varchar2(200) path 'keyword()'
or
columns     keyword varchar2(200) path 'keyword/text()'
or
columns     keyword varchar2(200) path '//*keyword()'
and
columns     keyword varchar2(200) path '/.keyword'

We are running the: Oracle Database 11g Enterprise Edition 11.2.0.4.0 64bit Production

Can somebody help me to find the error?

Thanks!

Upvotes: 0

Views: 3290

Answers (1)

Boneist
Boneist

Reputation: 23578

After tweaking your xml data a lot, and commenting out the namespace bit (which made it return no rows for me), I managed to get the output I think you're after:

with testtable2 as (select xmltype(
'<records>
  <REC r_id_disclaimer="yyyyyyy">
     <UID>xxxxxxx</UID>
     <static_data>
       <keywords count="4">
           <keyword>Brain</keyword>
           <keyword>Depres</keyword>
           <keyword>Acute</keyword>
           <keyword>Chine</keyword>
       </keywords>
     </static_data>
  </REC>
</records>') xml_file from dual)
select RecUid.CD_UID,
       Abst2.*
from   testtable2 t
       cross join xmltable(--xmlnamespaces(default 'http://scientific.thomsonreuters.com/schema/wok5.4/public/FullRecord'),
                           'records/REC'
                           passing t.xml_file 
                           columns CD_UID varchar2(200) path 'UID',
                                   --names xmltype path 'static_data/summary'
                                   Abstract xmltype path 'static_data/keywords'
                           ) RecUid
       cross join xmltable(--xmlnamespaces(default 'http://scientific.thomsonreuters.com/schema/wok5.4/public/FullRecord'),
                           'keywords/keyword'
                           passing RecUid.Abstract
                           columns keyword varchar2(200) path '.'
                           ) Abst2;

CD_UID               KEYWORD             
-------------------- --------------------
xxxxxxx              Brain               
xxxxxxx              Depres              
xxxxxxx              Acute               
xxxxxxx              Chine

Upvotes: 2

Related Questions