This discussion is archived
2 Replies Latest reply: Jan 30, 2013 10:25 AM by 983611 RSS

Issue with using XML TYPE

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,
  • 1. Re: Issue with using XML TYPE
    odie_63 Guru
    Currently Being Moderated
    Hi,

    You're close.

    Since you want to extract a sequence of <nextredentitycode> elements in the second XMLTable then its XQuery expression must be :
    /nextredentitycodes/nextredentitycode
    Full query :
    SQL> SELECT x1.entitycode
      2       , x4.nextredentitycode
      3  FROM CME_CDS t
      4     , XMLTABLE(
      5        '/entity'
      6        PASSING t.XML_TYPE
      7        COLUMNS entitycode VARCHAR2(128) path 'redentitycode'
      8              , Main_list  XMLTYPE PATH 'events/event/corporateaction/nextredentitycodes'
      9       ) x1
     10     , XMLTABLE(
     11        '/nextredentitycodes/nextredentitycode'
     12        PASSING x1.Main_list
     13        COLUMNS nextredentitycode varchar2(128) path '.'
     14       ) x4
     15  WHERE t.ID_ = 5 ;
     
    ENTITYCODE           NEXTREDENTITYCODE
    -------------------- --------------------
    4E363M               GK6295
    4E363M               GK9D9E
    4E363M               GJ58BA
    (please note that I had to remove the initial /data root element to test, it doesn't appear in your sample data - adjust as necessary)
  • 2. Re: Issue with using XML TYPE
    983611 Newbie
    Currently Being Moderated
    Thank you very much for your answer...
    I see it now that when the field names (tag name) are same, the path should be '.'
    I used the tag name, that was why it didn't work.


    Really appreciate your help!

Legend

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