We can use SQL to write some SQL for us, for example:
SQL> create table t (id int, xml clob);
Table created.
SQL>
SQL> insert into t values (7369,
2 q'{<?xml version='1.0' encoding='UTF8' ?>
3 <ROW>
4 <COLUMN NAME="EMPNO"><![CDATA[7369]]></COLUMN>
5 <COLUMN NAME="ENAME"><![CDATA[SMITH]]></COLUMN>
6 <COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
7 <COLUMN NAME="SAL"><![CDATA[800]]></COLUMN>
8 </ROW>}');
1 row created.
SQL>
SQL>
SQL> insert into t values (7499,
2 q'{<?xml version='1.0' encoding='UTF8' ?>
3 <ROW>
4 <COLUMN NAME="EMPNO"><![CDATA[7499]]></COLUMN>
5 <COLUMN NAME="ENAME"><![CDATA[ALLEN]]></COLUMN>
6 <COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
7 <COLUMN NAME="SAL"><![CDATA[1600]]></COLUMN>
8 </ROW>}');
1 row created.
SQL>
SQL> insert into t values (7521,
2 q'{<?xml version='1.0' encoding='UTF8' ?>
3 <ROW>
4 <COLUMN NAME="EMPNO"><![CDATA[7521]]></COLUMN>
5 <COLUMN NAME="ENAME"><![CDATA[WARD]]></COLUMN>
6 <COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
7 <COLUMN NAME="SAL"><![CDATA[1250]]></COLUMN>
8 </ROW>}');
1 row created.
SQL>
SQL> insert into t values (7566,
2 q'{<?xml version='1.0' encoding='UTF8' ?>
3 <ROW>
4 <COLUMN NAME="EMPNO"><![CDATA[7566]]></COLUMN>
5 <COLUMN NAME="ENAME"><![CDATA[JONES]]></COLUMN>
6 <COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
7 <COLUMN NAME="SAL"><![CDATA[2975]]></COLUMN>
8 </ROW>}');
1 row created.
SQL>
SQL> insert into t values (7654,
2 q'{<?xml version='1.0' encoding='UTF8' ?>
3 <ROW>
4 <COLUMN NAME="EMPNO"><![CDATA[7654]]></COLUMN>
5 <COLUMN NAME="ENAME"><![CDATA[MARTIN]]></COLUMN>
6 <COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
7 <COLUMN NAME="SAL"><![CDATA[1250]]></COLUMN>
8 </ROW>}');
1 row created.
SQL>
SQL>
SQL> select
2 'spool /tmp/f'||id||'.xml'||chr(10)||
3 'select xml from t where id = '||id||';'||chr(10)||
4 'spool off'
5 from t
6
SQL> spool /tmp/unloader.sql
SQL> /
spool /tmp/f7369.xml
select xml from t where id = 7369;
spool off
spool /tmp/f7499.xml
select xml from t where id = 7499;
spool off
spool /tmp/f7521.xml
select xml from t where id = 7521;
spool off
spool /tmp/f7566.xml
select xml from t where id = 7566;
spool off
spool /tmp/f7654.xml
select xml from t where id = 7654;
spool off
5 rows selected.
SQL> spool off
SQL>
SQL> alter session set cursor_sharing = force;
Session altered.
SQL> set pages 0
SQL> set lines 2000
SQL> set termout off
SQL> set trimspool on
SQL> set long 50000
SQL> set longchunksize 50000
SQL> @/tmp/unloader.sql
SQL> set termout on
SQL> alter session set cursor_sharing = exact;
Session altered.
SQL>
SQL> host dir c:\tmp\*.xml
Volume in drive C is System
Volume Serial Number is 7AD7-C05A
Directory of c:\tmp
17/10/2016 10:00 AM 300 f7369.xml
17/10/2016 10:00 AM 301 f7499.xml
17/10/2016 10:00 AM 300 f7521.xml
17/10/2016 10:00 AM 301 f7566.xml
17/10/2016 10:00 AM 302 f7654.xml
5 File(s) 1,504 bytes
SQL>