BTW, I can't imagine any of the below is legible, but I find myself enjoying these types of things.

  with bgu as 
  (select
    protocol_code,
    application_code,
    description,
    lab,
    proj."seqno",
    proj."project",
    proj."protocol",
    'false' "nexttag",
    'true' "materialtag"
  from
    protocol,
    xmltable('for $a in /*
                 for $n at $nidx in //*/MATERIAL
                 return <result>
                 <project>{fn:data($n/@project)}</project>
                 <protocol>{fn:data($n/@protocol)}             </protocol>
                      </result>'
             passing xmltype(protocol.xml)
             columns
                 "seqno" for ordinality,
                 "project" varchar2(100),
                 "protocol" varchar2(100)) proj
  where
    active = 'Y'
  union
  select
    protocol_code,
    application_code,
    description,
    lab,
    proj."seqno",
    proj."project",
    proj."protocol",
    'true' "nexttag",
    'false' "materialtag"
  from
    protocol,
    xmltable('for $a in /*
                 for $n at $nidx in //*/NEXT
                 return <result>
                 <project>{fn:data($n/@project)}</project>
                 <protocol>{fn:data($n/@protocol)}</protocol>
                      </result>'
             passing xmltype(protocol.xml)
             columns
                 "seqno" for ordinality,
                 "project" varchar2(100),
                 "protocol" varchar2(100)) proj
  where
    active='Y'
  order by 1,2,4 )
  select 
    * 
  from 
    (select distinct 
          protocol_code,
          sys_connect_by_path(protocol_code,'||||'),
          connect_by_isleaf "isleaf"
    from bgu
    where protocol_code like 'PROTNAME%'
    connect by nocycle prior "protocol" = protocol_code)
  where "isleaf"=1