Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table xmltable(xmlpref) as
select '<ParentProfiler>
  <SettingExp>
    <Entity name="companycode" type="robot" value="fn8ks92in"></Entity>
    <Entity name="locationcode" type="dummy" value="CO"></Entity>
    <Entity name="companycode" type="dummy" value="0xm9n4mdk"></Entity>
  </SettingExp>
</ParentProfiler>' from dual
1 rows affected

select z.test1, z.test2, b.XMLPref 
,extractvalue(xmltype(b.XMLPref),'//ParentProfiler/SettingExp/Entity[@name="companycode"]/@value[0]') as TestingOldMethod
from xmltable b,
XMLTABLE('/ParentProfiler/SettingExp'
    PASSING XMLTYPE(b.XMLPref)
    COLUMNS test1  VARCHAR2(100)  PATH 'Entity[@name="companycode"]/text()' --'Entity/text()'
           ,test2  VARCHAR2(100)  PATH 'Entity[1]' -- 'Values/string-join(./Entity/text(), ",")',
    ) z


TEST1
TEST2
XMLPREF
TESTINGOLDMETHOD


null
null
<ParentProfiler>
  <SettingExp>
    <Entity name="companycode" type="robot" value="fn8ks92in"></Entity>
    <Entity name="locationcode" type="dummy" value="CO"></Entity>
    <Entity name="companycode" type="dummy" value="0xm9n4mdk"></Entity>
  </SettingExp>
</ParentProfiler>
null


select x.value
from xmltable b
cross apply XMLTABLE(
  '/ParentProfiler/SettingExp/Entity[@name="companycode"]'
  PASSING XMLTYPE(b.XMLPref)
  COLUMNS value  VARCHAR2(100)  PATH '@value'
) x


VALUE


fn8ks92in

0xm9n4mdk