3 Replies Latest reply on Mar 12, 2010 7:41 PM by Azhar Husain

    XMLtype to CLOB conversion

    Azhar Husain
      Hi Gurus,

      I am trying to convert XML file stored into XMLtype datatype into CLOB through getClobVal function but its not able to write complete text (which is around 1 MB ) into CLOB. When I am checking my XML file after conversion the TEXT tag is not same as it was in the original XML file. In other words-

      I have a table TEST_TABLE(TEST_COL XMLtype)
      I wrote following function(just pasting the part of code, though I can use getclobval() directly into query) to convert XML store into TEXT_TABLE

      CREATE OR REPLACE FUNCTION getvalue_clob_post(p_xmldata XMLtype)
      RETURN clob IS
      v_xmldata XMLtype;
      BEGIN
      v_xmldata:=p_xmldata;
      RETURN v_xmldata.getClobVal();
      EXCEPTION WHEN OTHERS THEN
      raise_application_error (-20102, 'Exception occurred in getAccountsHTML :'||SQLERRM);
      END getvalue_clob_post;
      /

      My problem is that I dont want to use getClobVal() becuase it is not working for the tags which contains very large text or different character set.
      Does any one has any idea how I can convert data(XML files) stored into XMLtype column into CLOB without using getClobVal() function?
      thanks in advance
        • 1. Re: XMLtype to CLOB conversion
          Jason_(A_Non)
          As for differences, not sure what you are seeing but this test doesn't produce any differences.
          Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
           
          SQL> set serveroutput on;
          SQL> 
          SQL> declare
            2    -- Local variables here
            3    k_data        CONSTANT VARCHAR2(10) := '1234567890';
            4    l_input_clob  CLOB;
            5    l_output_clob CLOB;
            6    p_xmldata     XMLtype;
            7  begin
            8    -- Test statements here
            9    for i in 1..100000
           10    loop
           11      l_input_clob := l_input_clob || k_data;
           12    end loop;
           13    dbms_output.put_line('Input Length: ' || length(l_input_clob));
           14    p_xmldata := XMLTYPE('<root>'||l_input_clob||'</root>');
           15    l_output_clob := p_xmldata.getClobVal();
           16    dbms_output.put_line('Output Length: ' || length(l_output_clob));
           17    dbms_output.put_line(substr(l_output_clob, 1, 106));  -- show start
           18    dbms_output.put_line('  ' ||substr(l_output_clob, 999903));  -- show end
           19  
           20  end;
           21  /
           
          Input Length: 1000000
          Output Length: 1000013
          <root>1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
            78901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890</root>
           
          PL/SQL procedure successfully completed
           
          SQL> 
          As for character sets, pay attention to answers from mdrake in
          {thread:id=242893}
          {thread:id=384328}
          I found those via
          http://www.google.com/search?&q=getClobVal+character+set+site%3Aforums.oracle.com
          • 2. Re: XMLtype to CLOB conversion
            Azhar Husain
            Thanks for giving reply

            Actually here in the example, you took simple text/number. Before posting this question, I tried following

            1. Remove all original text from the original XML file(say file-1) and saved it into file-2(say).
            2. Added 4 times simple text (as compare to file-1) into file-2.
            3. Tested the getClobVal() function.

            Result : It worked fine with file-2(Was containing 4 times text then the file-1)

            when I am testing getClobVal() with original text(remember this original text containing lot of scientific and different other characters, for example , [“ex herb. D. Petrie No. 73”—c. ♂ (CHR).] ) the getClobVal() is not returning all the data(its retuning partial data). Following are some facts about text

            Character as Unicode: 909,192
            Character as ANSI: 909,192
            Words 143,651
            Lines -1

            I searched all along Google and found a link which matches with my issue but did not get any solid solution.

            http://www.orafaq.com/forum/t/102098/2/
            • 3. Re: XMLtype to CLOB conversion
              Azhar Husain
              In addition to above

              I used XMLSERIALIZE function (As this function also return the XML document into CLOB) but the session is getting hanged when I am trying XMLserialize function for the same document.

              select XMLSerialize(document xml_doc as clob)
              as xmlserialize_doc
              from Table_name
              where <condition>

              Seems this function also using getclobval() function internally.

              Talked to Oracle and they are able to reproduce the issue and log Bug 9468270.