6 Replies Latest reply: Jul 7, 2012 10:56 AM by user12144220 RSS

    How do I convert blob to xml string?

    user12144220
      Hello everyone,

      I need a help to read a xml string larger than 4000 in stored procedure.

      CREATE OR REPLACE PROCEDURE PROD.InsertFDataBlob(V_MYBLOB BLOB) AS

      BEGIN

      ........

      passing SYS.XMLTYPE(V_MYBLOB)

      .......

      I could not find a simple way to convert my blob value which is V_MYBLOB to varchar. My database versions are 10g and 11g.


      Thank you.
        • 1. Re: How do I convert blob to xml string?
          AlexAnd
          >
          First convert BLOB to CLOB. Follow the below link for that purpose

          BLOB to CLOB?

          Once you are done with that i think XMLTYPE accpets CLOB.
          >
          by Karthick_Arp https://kr.forums.oracle.com/forums/thread.jspa?threadID=1009608

          then clob to xml

          based on above
          SQL> select * from v$version where rownum=1;
           
          BANNER
          ----------------------------------------------------------------
          Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
           
          SQL> create table blob_xml(id number, col blob);
           
          Table created
           
          SQL>  -- insert data into blob_xml
          SQL> select * from blob_xml;
           
                  ID COL
          ---------- ---
                   1 <BL
           
          SQL> 
          SQL> 
          SQL> declare
            2    v_clob    CLOB;
            3    v_varchar VARCHAR2(32767);
            4    v_start   PLS_INTEGER := 1;
            5    v_buffer  PLS_INTEGER := 32767;
            6    blob_in   blob;
            7    x         xmltype;
            8  BEGIN
            9    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
           10  
           11    select col into blob_in from blob_xml where id = 1;
           12  
           13    FOR i IN 1 .. CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer) LOOP
           14  
           15      v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in,
           16                                                            v_buffer,
           17                                                            v_start));
           18  
           19      DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
           20  
           21      v_start := v_start + v_buffer;
           22    END LOOP;
           23  
           24    x := xmltype.createxml(v_clob);
           25    dbms_output.put_line(x.getclobval());
           26  
           27  end;
           28  /
           
          <BillOfMaterialItem revisionIdentifier="--" billOfMaterialItemIdentifier="645-173-1" billOfMaterialItemUniqueIdentifier="IVI123019387" itemQuantity="6" globalProductQuantityTypeCode="PerAssembly" globalProductQuantityTypeCodeOther="" notes="" description="3/8" proprietarySequenceIdentifier="115510509">
               <AdditionalAttributes groupLabel="BOM Information">
                    <AdditionalAttribute name="Line Number" value="20" dataType="Float" /> 
               </AdditionalAttributes>
          </BillOfMaterialItem>
          
           
          PL/SQL procedure successfully completed
           
          SQL>
          • 2. Re: How do I convert blob to xml string?
            odie_63
            We don't need all that stuff.

            The XMLType object already provides a constructor working on a BLOB directly.
            We just have to pass in the charset ID in addition to the binary content :

            For example, if the content is encoded in UTF-8 :
            ...
            passing xmltype(V_MYBLOB, nls_charset_id('AL32UTF8'))
            ...
            http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/t_xml.htm#i1009842
            • 3. Re: How do I convert blob to xml string?
              AlexAnd
              make a note :)
              many thanks
              • 4. Re: How do I convert blob to xml string?
                user12144220
                Thank you for help.

                And one question regarding this, please. What is the maximum length of V_MYBLOB in my case? In other words, what is the maximum length of my xml string ?

                Thank you.
                • 5. Re: How do I convert blob to xml string?
                  odie_63
                  What is the maximum length of V_MYBLOB in my case?
                  More than you probably ever need ;)

                  BLOB can go up to 128 TB in PL/SQL, and (4 GB - 1) * database_block_size in SQL.

                  http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/datatypes.htm#CHDEDFJE
                  • 6. Re: How do I convert blob to xml string?
                    user12144220
                    Thank you.