2 Replies Latest reply: May 1, 2013 10:15 PM by 873069 RSS

    Oracle xml_dom.writeToClob ignore the character set

    873069
      I'm trying to generate a xml file using oracle. After generating the xml using dbms_xmldom, it is stored in a CLOB and later written to a table.

      The problem is that the character set ('UTF-8') is not included in the header even though it is specified using setCharset() procedure.

      Following is the oracle script,

      declare
      export_file_ CLOB ;
      str_export_file_ xmldom.DOMDocument;
      main_node xmldom.DOMNode;
      root_node xmldom.DOMNode;
      root_elmt xmldom.DOMElement;

      begin

      str_export_file_ := xmldom.newDOMDocument;
      xmldom.setVersion(str_export_file_, '1.0');
      xmldom.setCharset(str_export_file_ , 'UTF-8');
      main_node := xmldom.makeNode(str_export_file_);
      root_elmt := xmldom.createElement(str_export_file_,'TextTranslation');
      xmldom.setAttribute( root_elmt, 'version' ,'1.0');
      xmldom.setAttribute( root_elmt, 'language' ,'ja');
      xmldom.setAttribute( root_elmt, 'module' ,'DEMOAND');
      xmldom.setAttribute( root_elmt, 'type' ,'VC');
      root_node := xmldom.appendChild(main_node, xmldom.makeNode(root_elmt));

      export_file_ :=' ';
      xmldom.writeToClob( str_export_file_,export_file_,'UTF-8');

      dbms_output.put_line ( export_file_ );
      end;
      The output is ,

      <?xml version="1.0"?>
      <TextTranslation version="1.0" language="ja" module="DEMOAND" type="VC"/>
      If anybody can suggest me what I have done incorrectly that will be great.

      Thanks in advance.
        • 1. Re: Oracle xml_dom.writeToClob ignore the character set
          odie_63
          The character set you specify via setCharset() procedure is ignored unless you use writeToFile() later.

          http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_xmldom.htm#CHDCGDDB
          Usage Notes

          This is used for WRITETOFILE Procedures if not explicitly specified at that time.
          You can also use something like this :
          SQL> set serveroutput on
          SQL> 
          SQL> declare
            2  
            3   export_file  clob;
            4   prolog       clob := '<?xml version="1.0" encoding="UTF-8"?>';
            5  
            6  begin
            7  
            8    select prolog || chr(10) ||
            9           xmlserialize(document
           10             xmlelement("TextTranslation"
           11             , xmlattributes(
           12                 '1.0' as "version"
           13               , 'ja'  as "language"
           14               , 'DEMOAND' as "module"
           15               , 'VC' as "type"
           16               )
           17             )
           18             indent
           19           )
           20    into export_file
           21    from dual ;
           22  
           23    dbms_output.put_line ( export_file );
           24  
           25  end;
           26  /
           
          <?xml version="1.0" encoding="UTF-8"?>
          <TextTranslation version="1.0" language="ja" module="DEMOAND" type="VC"/>
          
           
          PL/SQL procedure successfully completed
           
          • 2. Re: Oracle xml_dom.writeToClob ignore the character set
            873069
            Thanks for the reply.

            It is working fine with your solution now. But the another problem came to me is that in writeToClob procedure character set is included as 'UTF-8',

            writeToClob( str_export_file_,export_file_,'UTF-8')

            but the header file does not include the character set.

            Thanks again.

            Edited by: Prageeth on May 1, 2013 8:15 PM