XML nodes over 64K in size cannot be printed when using Binary XML Storage [Oracle] [Solved]

If you're using Binary XML Storage and you used DBMS_XSLPROCESSOR's CLOB2FILE method to write to file, there is a chance that you'll encounter the 64K limit of nodes.

The workaround is to use another method to write XMLType to file: DBMS_XMLDOM's WRITETOFILE:

Declare your desired directory:

create or replace DIRECTORY XMLDIR AS 'C:\tmp';

Use dbms_xmldom to write XMLType column to file:

declare
  doc DBMS_XMLDOM.DOMDocument;
BEGIN
  for c in (select xml_col from xml_table x where key_col='mykey') loop
    doc := DBMS_XMLDOM.NewDomDocument(c.xml_col);
    DBMS_XMLDOM.WRITETOFILE(doc, 'XMLDIR/output.xml');
end loop;
/