Database Software

We are currently outputting our XML to file by converting the XMLTYPE data to a DOMDocument (DBMS_XMLDOM package) and then using the DBMS_XMLDOM.WRITETOFILE procedure. This works just fine and is nice and simple to use.

However, we have now had a request that we include the xmlroot information at the top of the XML file i.e.

<?xml version="1.0" encoding="UTF-8" ?>

We're having problems including this in our XMLType data using something like:

SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8" ?>'||v_xmltypedata) INTO v_xmltypedata FROM DUAL;

(we can't use XMLROOT method as this doesn't include the encoding attribute for some reason!?!?)

because we get a "string buffer too small" error (it's fine if we do this with small xml data, but when they get larger (i.e. real data) it's generating the error).

Now our problem is that even if we could get the root information within the XMLTYPE, when we convert it to a DOMDocument and then write this out to a file this root processing instruction is lost.

We noticed in the DBMS_XMLDOM package that there is SetVersion and SetStandalone functions (the ones you can set with XMLROOT), but even if we call these functions on our DOMDocument before outputting to file, then the file still has no root information against it. Is this correct? Surely if we set the version and/or the standalone settings then this should be reflected in the document that generated in the file? Also we were hoping that specifying the characterset in the WriteToFile function this may generate the root with the encoding attribute, but we haven't even bothered trying that yet as we can't even get the version to be output.

Are we really going to have to result in the messy way of converting our XMLTYPE data to a Clob and output it to file in chunks using UTL_FILE?

Thanks in advance for any help or advice.