Xpath query in oracle from a Drive

Xpath query in oracle from a Drive


DECLARE
v_OutFile utl_file.file_type;
v_xml clob;
x XMLTYPE;
v_data varchar2(4000);
begin
v_xml := null;
/*************************************************************/
v_OutFile := utl_file.fopen( 'MY_XML','FHR'||'.xml', 'r' );
if utl_file.is_open(v_OutFile) then
loop
utl_file.get_line(v_OutFile,v_data);
v_xml:= v_xml||chr(10)||v_data;
end loop;
end if;
utl_file.fclose(v_OutFile);
/*************************************************************/

Exception
When no_data_found then
utl_file.fclose(v_OutFile);

x := XMLTYPE(v_xml);


FOR i IN ( SELECT EXTRACTVALUE(VALUE(t),'/FHR/PRIORITYCODE') FHR_1,
EXTRACTVALUE(VALUE(t),'/FHR/IMMEDIATEDESTINATION') FHR_2,
EXTRACTVALUE(VALUE(t), '/FHR/IMMEDIATEORIGIN') FHR_3,
EXTRACTVALUE(VALUE(t), '/FHR/CREATIONDATE') FHR_4,
EXTRACTVALUE(VALUE(t), '/FHR/CREATEIONTIME') FHR_5,
EXTRACTVALUE(VALUE(t), '/FHR/FILEIDMODIFIER') FHR_6,
EXTRACTVALUE(VALUE(t), '/FHR/FORMATCODE') FHR_7,
EXTRACTVALUE(VALUE(t), '/FHR/IMMEDIATEDESTINATIONNAME') FHR_8,
EXTRACTVALUE(VALUE(t), '/FHR/IMMEDIATEORIGINNAME') FHR_9,
EXTRACTVALUE(VALUE(t), '/FHR/REFERENCECODE') FHR_10
FROM TABLE(XMLSEQUENCE(EXTRACT(x,'/BEFTN/FHR/FHR'))) t )
LOOP
--- dbms_output.put_line('B_1 = ' || i.b_1 || 'b_2='|| i.b_2);
dbms_output.put_line(
i.FHR_1 ||
i.FHR_2 ||
i.FHR_3 ||
i.FHR_4 ||
i.FHR_5 ||
i.FHR_6 ||
i.FHR_7 ||
i.FHR_8 ||
i.FHR_9 ||
i.FHR_10
);

END LOOP;
/*
-- '/FCR/BATCHCOUNT
FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '/FCR/BATCHCOUNT') b_1,
EXTRACTVALUE(VALUE(t), '/FCR/ENTRYHASH') b_2
FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/FCR/FCR'))) t )
LOOP
dbms_output.put_line('B_1 = ' || i.b_1 || 'b_2='|| i.b_2);
END LOOP;
*/

END;