1 Reply Latest reply: Aug 23, 2013 5:07 AM by odie_63 RSS

    a blob column in xml

    HenkB


      Hi,

       

      I have a blob column in some table. It is for example an jpg image. The size is more than 32k (for example size:152287 with dbms_lob.getlength)

       

      How can I get this column in a xml document because I want to use the xml file for importing in a different Oracle schema.

       

      Thanks in advance,

       

      Henk

        • 1. Re: a blob column in xml
          odie_63

          Extracting the BLOB is not the hardest part, SQL/XML function such as XMLElement() can read the content natively and convert it to a text node in xs:hexBinary format :

          SQL> select xmlelement("Image", image)

            2  from test_jdbc_blob

            3  where img_id = 'Koala.jpg' ;

           

          XMLELEMENT("IMAGE",IMAGE)

          --------------------------------------------------------------------------------

          <Image>FFD8FFE000104A46494600010201006000600000FFEE000E41646F626500640000000001F

          Other option is to convert the BLOB to base64 and feed it to an element similarly, though it requires a little more code.

          DBMS_XMLDOM has some methods to manipulate large binary streams too.