A few questions to help us identifying the problem :
What's the db version ?
How large are your XML files ?
Are they truncated to the same size ?
Why are you using dynamic SQL here ? I don't see any reason for it. Why not just a simple CURSOR FOR LOOP ?
FOR r IN ( select rownum id, xml_data from tablex ) LOOP
dbms_xslprocessor.clob2file(r.xml_data.getClobVal(), 'XML_DIR', 'test' || r.id || '.xml');
1. DB Version - Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - 64bit
2. Failed XML file size varies -- 36077 bytes - 97484 bytes
3, They are not truncated to same size, but noticed all of the failed files are loosing last few characters of the last line Ex: "</rootEleme"
I have tried with the CURSOR and i still see the issue.
And i found files causing this issue, but not sure whats the issue..