create table identity (IDENTITY_ID varchar2(100));

Insert into identity values ('100');
Insert into identity values ('101');



create table CM_ENCOUNTER (IDENTITY_ID varchar2(100), CSN varchar2(100));

Insert into CM_ENCOUNTER values ('100','3a1');
Insert into CM_ENCOUNTER values ('101','1a2');

create table CM_PATIENT (IDENTITY_ID varchar2(100), GENDER varchar2(100), race varchar2(100));

Insert into CM_PATIENT values ('100','F','AA');
Insert into CM_PATIENT values ('101','M','HA');
Insert into CM_PATIENT values ('101','F','EA');



create table CM_ENCOUNTER_RFV
 (IDENTITY_ID varchar2(100), CSN_ID varchar2(100), LINE varchar2(100), ENC_REASON_NAME  varchar2(100));
 

Insert into  CM_ENCOUNTER_RFV values ('100','23vx','2', 'phone');

commmit;

 <EvaluatePatient>
    <PatientInformation>
      <Patient>

          <mrn>100</mrn>
          <CSN>3a1</CSN>
          <Gender>F</Gender>
          <race>AA</race>
          <ID>23vx2</ID>
          <Name>phone</Name>
       </Patient>
     <Orders>   <!-- XML from orders here, if applicable --> </Orders>
     <Immunizations>    <!-- XML from orders here, if applicable --> </Immunizations>
     <ProblemList></ProblemList>
   </PatientInformation>

   <PatientInformation>
     <Patient> 
       <mrn>101</mrn>
       <CSN>1a2</CSN>
       <Gender>M</Gender>
       <race>HA</race>
       <ID></ID>
       <Name></Name>
      </Patient>
     <Orders>   <!-- XML from orders here, if applicable --> </Orders>
     <Immunizations>    <!-- XML from orders here, if applicable --> </Immunizations>
     <ProblemList></ProblemList>
   </PatientInformation>
</EvaluatePatient>

 Create table  temp_xml
as
  Select                     XMLELEMENT("PatientInformation",(XMLELEMENT("Patient", 
                              XMLELEMENT("mrn", s.identity_id),
                              XMLELEMENT("CSN", m.csn),
                              XMLELEMENT("Gender", p.gender),
                              XMLELEMENT("race", p.race),
                              XMLELEMENT("ID", r.csn_id || r.line),
                              XMLELEMENT("Name", r.enc_reason_name))),
                              XMLELEMENT ("Orders", NULL),
                              XMLELEMENT ("Immunizations", NULL),
                              XMLELEMENT ("ProblemList", NULL)) cola
        

  from identity s
  join CM_ENCOUNTER m
  on m.identity_id = s.identity_id
  join CM_PATIENT   p
    on m.identity_id = p.identity_id
  left outer join CM_ENCOUNTER_RFV   r
    on m.identity_id = r.identity_id;

DECLARE
  CURSOR c_data IS
SELECT * from temp_xml;

  v_file  UTL_FILE.FILE_TYPE;
BEGIN
  v_file := UTL_FILE.FOPEN(location     => 'PLSQL_DIR', -- DUMP_DIR
                           filename     => 'sample_xml.csv',
                           open_mode    => 'w',
                           max_linesize => 32767);
  utl_file.PUT_LINE( v_file, '<?xml version="1.0" encoding="utf-8" ?>' );
  utl_file.PUT_LINE( v_file, '<EvaluatePatient>' );
  FOR cur_rec IN c_data LOOP
    UTL_FILE.PUT_LINE(v_file, cur_rec.cola );  -- error here
  END LOOP;
  utl_file.PUT_LINE( v_file, '</EvaluatePatient>' );
  UTL_FILE.FCLOSE(v_file);
END;  
  

DECLARE

   -- Data Variables
   v_xml              XMLTYPE;
   v_blob             BLOB;
   v_data_length      NUMBER; 

   -- Loop Control Variables
   v_offset           NUMBER             DEFAULT 1;
   v_chunk   CONSTANT NUMBER             DEFAULT 4000;

   -- UTL_FILE variables
   fh                 UTL_FILE.file_type;

BEGIN
   -- A big query, about 27k on my PC
   SELECT XMLELEMENT ("EvaluatePatient", XMLAGG (cola))
     INTO v_xml
     FROM temp_xml;

   -- Turn the XML into a BLOB to bypass any 4k and 32k issues
   v_blob := v_xml.getblobval (1);
   v_data_length := DBMS_LOB.getlength (v_blob);
   
   -- Open the file
   fh := UTL_FILE.fopen ('PLSQL_DIR', 'myxml.xml', 'wb', v_chunk);

   -- Da loop de loop
   LOOP
      -- Exit when our file offset is bigger than our file
      EXIT WHEN v_offset > v_data_length;

      -- Write the output chunk by chunk
      UTL_FILE.put_raw (fh, DBMS_LOB.SUBSTR (v_blob, v_chunk, v_offset),
                        TRUE);

      -- Increment the offset by the amount written
      v_offset := v_offset + v_chunk;
   END LOOP;

   -- Close the file and say byebye
   UTL_FILE.fclose (fh);

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      -- We won't write any data, or even open the file,
      -- if the query return no rows
      NULL;
END;