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;
/

Write XMLType Column to File using dbms_xslprocessor [Oracle] [Solved]

Declare your desired directory:

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

Use dbms_xslprocessor to write XMLType column to file:

BEGIN
  for cur in (select XML_COLUMN from YOUR_TABLE) loop
    dbms_xslprocessor.clob2file(cur.XML_COLUMN.getClobVal(), 'XMLDIR', 'NAME.xml');
  end loop;
END;
/