This discussion is archived
2 Replies Latest reply: Jan 31, 2013 8:23 AM by Jason_(A_Non) RSS

how to overcome ORA-19279 error

va*447258*15 Newbie
Currently Being Moderated
Hi,

I have oracle 11.2.2 and following xml is loaded in xmltype table and trying to retrive data and getting following error, please anyone could help me to fix this error
<?xml version="1.0" encoding="utf-8"?>
<agents count="1382">
<agent>
<name>Nancy Palmer</name>
<email>npalmer@apr.com</email>
<agentid>MLSL:00525350</agentid>
<officeid>58</officeid>
<website>http://www.nancypalmer.com</website>
<photo>https://sites.e-agents.com/Uploads/68/41/6841/Agents/agent_8418_NANCY_PALMER_COLOR_HEAD_SHOT_HIGH_QUALITY_2011.jpg</photo>
<phone_direct>6504344313</phone_direct>
<phone_cell>6504920200</phone_cell>
<mod_time>2012-08-31T05:15:06.933</mod_time>
</agent>

<agent>
<name>Genella Williamson</name>
<email>genella@apr.com</email>
<agentid>MLSL:00755754</agentid>
<officeid>58</officeid>
<website>http://www.apr.com/genella</website>
<photo>https://sites.e-agents.com/Uploads/68/41/6841/Agents/agent_8426_genella.jpg</photo>
<phone_direct>6504344319</phone_direct>
<phone_cell>6507870839</phone_cell>
<mod_time>2010-10-30T15:15:07.603</mod_time>
</agent>

<agent>
<name>Diana Langley</name>
<email>dlangley@apr.com</email>
<agentid>MLSL:01256202,SFAR:805608</agentid>
<officeid>50</officeid>
<website>http://www.apr.com/DLangley</website>
<photo>https://sites.e-agents.com/Uploads/68/41/6841/Agents/agent_7848_dlangley.jpg</photo>
<phone_direct/>
<phone_cell/>
<mod_time>2011-06-06T05:15:06.587</mod_time>
</agent>
</agents>


query usered to reterive data

SELECT count, NAME, email,
       officeid, website,
       photo, phone_direct,
       phone_cell, mod_date
  FROM TEMP_XML tx,
       XMLTable('/agents'
                PASSING tx.xml_data  
                columns count        varchar2(30) path '@count',
                        NAME         VARCHAR2(100) path 'agent/name',
                         email        VARCHAR2(100) path 'agent/email',
                         officeid     VARCHAR2(100) path 'agent/officeid',
                         website      VARCHAR2(100) path 'agent/website',
                         photo        VARCHAR2(100) path 'agent/photo',
                         phone_direct      VARCHAR2(100) path 'agent/phone_direct',
                         phone_cell      VARCHAR2(100) path 'agent/phone_cell',
                         mod_date      VARCHAR2(100) path 'agent/mod_date'
                        ) 

recieved error 

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
19279. 00000 -  "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.
Thanks in advance

Best Regards,
  • 1. Re: how to overcome ORA-19279 error
    AlexAnd Guru
    Currently Being Moderated
    use
    SELECT count,
           NAME,
           email,
           officeid,
           website,
           photo,
           phone_direct,
           phone_cell,
           mod_date
      FROM TEMP_XML tx,
           XMLTable('agents' PASSING tx.xml_data 
                    columns count varchar2(30) path '@count',
                    xml_part xmltype path '*') x,
           XMLTable('agent' PASSING x.xml_part 
                    columns NAME VARCHAR2(100) path 'name',
                    email VARCHAR2(100) path 'email',
                    officeid VARCHAR2(100) path 'officeid',
                    website VARCHAR2(100) path 'website',
                    photo VARCHAR2(100) path 'photo',
                    phone_direct VARCHAR2(100) path 'phone_direct',
                    phone_cell VARCHAR2(100) path 'phone_cell',
                    mod_date VARCHAR2(100) path 'mod_date') y
  • 2. Re: how to overcome ORA-19279 error
    Jason_(A_Non) Expert
    Currently Being Moderated
    Just a comment so the OP knows. The valid example
                    xml_part xmltype path '*') x,
    passes along all the child nodes of the "agents" node.

    If the example had been written
                    xml_part xmltype path 'agent') x,
    then only the "agent" child nodes of "agents" are passed along.

    The second XMLTable only looks for "agent" nodes in what it receives from the first XMLTable, so the result is the same in both cases. The only difference is that less would be passed from the first XMLTable to the second XMLTable if the "agents" node had additional children besides the "agent" node. As the sample XML has only "agent" as a child of "agents", there is no difference in what is passed/processed.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points