This discussion is archived
1 Reply Latest reply: Jan 29, 2013 9:32 PM by rp0428 RSS

Problem with using XMLTYPE

983611 Newbie
Currently Being Moderated
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,

Legend

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