Writing XML to a file

Marco Gralike has posted a couple of items at the Amis blog and a later followup on his own blog about ways of writing XML to a file - comparing the use of CLOB2FILE with DBMS_XMLDOM.WRITETOFILE with DBMS_XSLPROCESSOR.CLOB2FILE - and finding the latter faster.

Surely this is simply because the input to the test is an XMLTYPE (the result of his test query).

• The call to CLOB2FILE requires an implicit conversion from XMLTYPE to CLOB and then simply serialising the CLOB (a large string already in XML format)
• WRITETOFILE is making an implicit conversion from XMLTYPE to DOMDOCUMENT - it is constructing a DOM tree and then walking it to produce the serialised output. Here's the signature for WRITETOFILE:

DBMS_XMLDOM.WRITETOFILE(
            doc IN DOMDOCUMENT,
            fileName IN VARCHAR2,
            charset IN VARCHAR2);

Building that tree is a major overhead - in this case - though obviously it wouldn't be if you actually needed to navigate around the tree adding/moving/updating or pruning nodes and branches.

Update 16-Feb:
Marco has continued the story here, in great detail and at great length (tkprof listings and all). He's particularly interested in file size; I'd be more interested in performance impact of the various steps in his operations. I will try to reproduce his tests and summarise those soon.