2 Replies Latest reply: Jan 31, 2013 10:23 AM by Jason_(A_Non) RSS

    how to overcome ORA-19279 error

    va*447258*15
      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
          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)
            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.