2 Replies Latest reply: Jan 30, 2013 12:25 PM by 983611 RSS

    Issue with using XML TYPE

    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,
        • 1. Re: Issue with using XML TYPE
          odie_63
          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
            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!