The main issue here that the XML functions available to you are designed for XMLTYPE columns not BLOB.
But you can convert the data as you go, eg
SQL> create table t ( pk int, x blob );
Table created.
SQL>
SQL> begin
2 insert into t values (1,utl_raw.cast_to_raw(
3 '<employees>
4 <employee>
5 <empno>7369</empno>
6 <ename>SMITH</ename>
7 <job>CLERK</job>
8 <hiredate>17-DEC-1980</hiredate>
9 </employee>
10 </employees>'
11 )
12 );
13
14 insert into t values (2,utl_raw.cast_to_raw(
15 '<employees>
16 <employee>
17 <ename>SMITH</ename>
18 <job>CLERK</job>
19 <hiredate>17-DEC-1980</hiredate>
20 </employee>
21 </employees>'
22 )
23 );
24
25 end;
26 /
PL/SQL procedure successfully completed.
SQL>
SQL> select extractvalue(x, '/employees/employee/empno')
2 from t;
select extractvalue(x, '/employees/employee/empno')
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
SQL> select pk, extractvalue(xmltype(utl_raw.cast_to_varchar2(x)), '/employees/employee/empno') eno
2 from t;
PK ENO
---------- --------------------
1 7369
2
For that many rows, this is going to take a long time, but if it is just a one-off operation (ie, application shutdown) then you can dump the results to a table (create-table-as-select) so you have a permanent record.
If the data you want is not present in a large percentage of rows, then you might get some benefits by checking for the tag directly against the blob before converting to XMLTYPE, eg
SQL> select pk, extractvalue(xmltype(utl_raw.cast_to_varchar2(x)), '/employees/employee/empno') eno
2 from t
3 where dbms_lob.instr(x,utl_raw.cast_to_raw('empno')) > 0;
PK ENO
---------- --------------------
1 7369