Oracle: How do I display DBMS_XMLDOM.DOMDocument for debugging?

Peter Goras picture Peter Goras · Jan 7, 2011 · Viewed 13.9k times · Source

Running Oracle 10g, Sqldeveloper 1.5.5

I want to view the contents of an DBMS_XMLDOM.DOMDocument as a string in the output or results window in sqldeveloper. or some other simple way to debug this thing...

Thanks,P

Answer

Rob van Laarhoven picture Rob van Laarhoven · Jan 7, 2011
DBMS_XMLDOM.WRITETOBUFFER  Writes the contents of the node to a buffer.
DBMS_XMLDOM.WRITETOCLOB    Writes the contents of the node to a CLOB.
DBMS_XMLDOM.WRITETOFILE    Writes the contents of the node to a file.

I have PL/SQL code that wites it to the file system using a DIRECTORY:

   dbms_xmldom.writeToFile(dbms_xmldom.newDOMDocument( xmldoc)
                                       ,'DATAPUMPDIR/myfile.xml') ;

I have created a function using dbms_xmldom.writetoclob

   create or replace function xml2clob (xmldoc XMLType) return CLOB is
     clobdoc CLOB := ' ';
   begin
     dbms_xmldom.writeToClob(dbms_xmldom.newDOMDocument( xmldoc)
                                       ,clobdoc) ;
     return clobdoc;
   end;
   /

Query:

SELECT xml2clob(Sys_Xmlagg(
         Xmlelement(Name "dummy"
                   ,dummy
                   ),Xmlformat('dual')))
   FROM dual;

Output:

<?xml version="1.0"?>
<dual>
  <dummy>X</dummy>
</dual>

You could try using a function like this:

   create or replace function dom2clob (domdoc  DBMS_XMLDOM.DOMDocument) return CLOB is
     clobdoc CLOB := ' ';
   begin
     dbms_xmldom.writeToClob(domdoc,clobdoc) ;
     return clobdoc;
   end;
   /