Friday, February 15, 2008

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:

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.


Marco Gralike said...

BTW It is "Marco". Apparently I was named (so the story goes)after an Italian bycicle sportsguy ;-)

Nigel said...

Sorry Marco - duly corrected. End of a long week :(