Unnamed

with t (educationeventid, xml_part, iter) as (
       select
               cast(educationeventid as varchar(255)) ,
               substr(substr(xml,641),
                       locate('<educationEventRef id="',
substr(rtrim(xml),641))), 1
       from hubble.educationevent
       where
       locate('<educationEventRef id="', substr(xml,641)) > 0
       union all
       select t.educationeventid,
           rtrim(substr(xml_part,
               locate('<educationEventRef id="', xml_part)+1)),
           iter+1
       from t
       where locate('<educationEventRef id="', xml_part) > 0
         and iter < 20
), tuples (t1, t2) as (
       select educationeventid,
               rtrim(substr(xml_part, locate('"', xml_part)+1,
               locate('"/>', xml_part) - (locate('"', xml_part)+1)))

       from t where iter > 1
)