1 Reply Latest reply: Jan 29, 2013 11:32 PM by rp0428 RSS

    Problem with using XMLTYPE

    983611
      Hello, I want to parse the fields out of an XML file with repeating tags <nextredentitycode>, and I'm having trouble with it. I can't get the XMLTYPE query below to work. My Oracle is version 11.2. And the CME_CDS is a table containing my file (stored as XML TYPE)

      The file has content like this:
           <entity>
                <referenceentity>ABC GL</referenceentity>
                <redentitycode>4E363M</redentitycode>
                <events>
                     <event>
                          <corporateaction>
                               <nextredentitycodes>
                                    <nextredentitycode>GK6295</nextredentitycode>
                                    <nextredentitycode>GK9D9E</nextredentitycode>
                                    <nextredentitycode>GJ58BA</nextredentitycode>
                               </nextredentitycodes>
                               <type>DEMERGER</type>
                               <eventdate>2006-10-10</eventdate>
                               <implementationdate>2011-12-16</implementationdate>
                          </corporateaction>
                     </event>
                </events>
           </entity>


      And I want to get the <redentitycode> and the corresponding 3 <nextredentitycode> out of this data.
      My query is as follows:

      SELECT x1.entitycode
      , x4.nextredentitycode
      FROM ETL.CME_CDS t
      , XMLTABLE(
      '/data/entity'
      PASSING t.XML_TYPE
      COLUMNS entitycode VARCHAR2(128) path 'redentitycode'
      , Main_list XMLTYPE PATH 'events/event/corporateaction/nextredentitycodes'

      ) x1,
      XMLTABLE(
      '/nextredentitycodes'
      PASSING x1.Main_list
      COLUMNS nextredentitycode varchar2(128) path 'nextredentitycode'
      ) x4
      WHERE t.ID_ = 5;

      ID = 5 is just where i store the xml type. But I keep on getting 'Expected singleton sequence - got multiple sequence' error.
      I suspect it's because of the multiple <nextredentitycode> tags using same name.

      Can you tell me if there is a way to parse this out?

      Much appreciated,