DECLARE
      F UTL_FILE.FILE_TYPE;
      MYCLOB CLOB;
  BEGIN
      SELECT
DBMS_XMLGEN.GETXML('SELECT FIRST_NAME, SALARY FROM EMP 
WHERE ROWNUM <= 5')
      INTO MYCLOB
      FROM DUAL;

      F := UTL_FILE.FOPEN('NEW','EMP_DEPT.XML','w',32767);
      UTL_FILE.PUT(F,MYCLOB);
      UTL_FILE.FCLOSE(F);
  END;
  /




SELECT extract(value(d), '//FIRST_NAME/text()').getStringVal() AS FIRST_NAME,
       extract(value(d), '//SALARY/text()').getNumberVal() AS SALARY
FROM   xml_tab x,
       table(xmlsequence(extract(x.xmlval, '/ROWSET/EMP'))) d;


CREATE TABLE T_XML_ORDERS(ORDERS XMLTYPE);

CREATE OR REPLACE PROCEDURE SP_PLACE_ORDERS
(
IN_ORDERS IN CLOB,
OUT_ORDER_DETAILS OUT SYS_REFCURSOR
)
AS
BEGIN

INSERT INTO T_XML_ORDERS VALUES (XMLTYPE(IN_ORDERS));

COMMIT;

OPEN OUT_ORDER_DETAILS FOR 
SELECT extract(value(d), '//EXCHANGE/text()').getStringVal() AS EXCHANGE,
       extract(value(d), '//ORDER_ID/text()').getNumberVal() AS ORDER_ID
FROM   T_XML_ORDERS x,
       table(xmlsequence(extract(x.ORDERS, '/ROWSET/ROW'))) d;


END;
/

VARIABLE X REFCURSOR;

DECLARE

I CLOB := '<ROWSET>
 <ROW>
  <FIRST_NAME>Steven</FIRST_NAME>
  <SALARY>25000</SALARY>
 </ROW>
 <ROW>
  <FIRST_NAME>Neena</FIRST_NAME>
  <SALARY>17000</SALARY>
 </ROW>
 <ROW>
  <FIRST_NAME>Dinesh</FIRST_NAME>
  <SALARY>17000</SALARY>
 </ROW>
 <ROW>
  <FIRST_NAME>Alexander</FIRST_NAME>
  <SALARY>9000</SALARY>
 </ROW>
 <ROW>
  <FIRST_NAME>Bruce</FIRST_NAME>
  <SALARY>6000</SALARY>
 </ROW>
</ROWSET>';

BEGIN

SP_PLACE_ORDERS(
 IN_ORDERS=>I,
 OUT_ORDER_DETAILS=>:X
);

END;
/


PRINT X;