DROP TABLE  some_things ;

create table some_things (object_value CLOB);

insert into some_things values
('<?xml version="1.0" encoding="UTF-8"?>
<indexAttributes>
    <attribute name="PENSION_APPLICATION.APPLICATION_ID" value="App123 "/>
    <attribute name="PERSON.ALT_IDENTIFIER" value="9046095052"/>
    <attribute name="PERSON.SSN" value="741852963 "/>
</indexAttributes>');

insert into some_things values
('<?xml version="1.0" encoding="UTF-8"?>
<indexAttributes>
    <attribute name="PENSION_APPLICATION.APPLICATION_ID" value="App256 "/>
    <attribute name="PERSON.ALT_IDENTIFIER" value="9046095052"/>
    <attribute name="PERSON.SSN" value="859632147 "/>
</indexAttributes>');



select extractValue(sys.xmltype.createXML(object_value),
                    '/indexAttributes.PERSON.SSN') ssn , 
                    extractValue(sys.xmltype.createXML(object_value),
'/indexAttributes.PENSION_APPLICATION.APPLICATION_ID')APPLICATION_ID
  from some_things;

select *  from   some_things;

SQL> col ssn format a10
SQL> col id format a10
SQL> select extractValue(value(x),'//attribute[@name="PERSON.SSN"]/@value') ssn,
  2         extractValue(value(x),'//attribute[@name="PENSION_APPLICATION.APPLICATION_ID"]/@value') id
  3  from some_things, table(xmlsequence(extract(xmltype(object_value),'/indexAttributes'))) x
  4  /
SSN        ID
---------- ----------
741852963  App123
859632147  App256

SQL> select extractValue(xmltype(object_value),
  2                      '//attribute[@name="PERSON.SSN"]/@value') ssn,
  3         extractValue(xmltype(object_value),
  4                      '//attribute[@name="PENSION_APPLICATION.APPLICATION_ID"]/@value') id
  5  from some_things
  6  /
SSN        ID
---------- ----------
741852963  App123
859632147  App256

SELECT APPLICATION_ID, SSN
FROM SOME_THINGS, XMLTABLE (
  '$X' PASSING xmltype(object_value, null, 1, 1) AS X 
  COLUMNS 
    APPLICATION_ID VARCHAR2(63) PATH '//attribute[@name="PENSION_APPLICATION.APPLICATION_ID"]/@value',
    SSN INT PATH '//attribute[@name="PERSON.SSN"]/@value'
);