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

    a blob column in xml




      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,



        • 1. Re: a blob column in xml

          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' ;





          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.