Oracle dbms_xmldom How to create a blob node?

Question

I do have the CR_ELEMENT procedure which gives me the possibility to add a node. I must create a blob node now and i was wondering how to do it with a procedure like CR_BLOB_ELEMENT.

Can someone give me a hint on how to do it ?

Regards,

Pierre

PS : I'm using Oracle Database 11g Release 11.2.0.4.0

 CR_BLOB_ELEMENT(l_domdoc in out dbms_xmldom.DOMDocument,
                               ref_node in dbms_xmldom.DOMNode,
                               nomel    in varchar2,
                               blobtobeadded   in blob)


   procedure CR_ELEMENT(l_domdoc in out dbms_xmldom.DOMDocument,
                           ref_node in dbms_xmldom.DOMNode,
                           nomel    in varchar2,
                           vnomel   in varchar2) as

        element  dbms_xmldom.DOMElement;
        node     dbms_xmldom.DOMNode;
        text     dbms_xmldom.DOMText;
        textnode dbms_xmldom.DOMNode;
      begin
        element  := dbms_xmldom.createElement(l_domdoc, nomel);
        node     := dbms_xmldom.appendChild(ref_node,
                                            dbms_xmldom.makeNode(element));
        text     := dbms_xmldom.createTextNode(l_domdoc, vnomel);
        textnode := dbms_xmldom.appendChild(node, dbms_xmldom.makeNode(text));

      end;

Answer 1

If useful to someone. Please find below an answer given written by odie_63 in community.oracle.com. This is working perfectly. Regards, Pierre

SQL> create table images (  
  2    id       integer  
  3  , name     varchar2(256)  
  4  , content  blob  
  5  );  

Table created  

SQL>  
SQL> declare  
  2   
  3    bf          bfile := bfilename('XML_DIR','base64.bin');  
  4    lob_ptr     blob;  
  5    dest_offs   integer := 1;  
  6    src_offs    integer := 1;  
  7   
  8  begin  
  9   
10    insert into images (id, name, content)  
11    values (1, 'Koala.jpg', empty_blob())  
12    returning content into lob_ptr;  
13   
14    dbms_lob.open(bf, dbms_lob.lob_readonly);  
15    dbms_lob.loadblobfromfile(lob_ptr, bf, dbms_lob.getlength(bf), dest_offs, src_offs);  
16    dbms_lob.close(bf);  
17   
18  end;  
19  /  

PL/SQL procedure successfully completed  

SQL> commit;  

Commit complete  

SQL>  
SQL> select id  
  2       , name  
  3       , utl_raw.cast_to_varchar2(dbms_lob.substr(content, 30)) as first_bytes  
  4       , dbms_lob.getlength(content) as lob_size  
  5  from images  
  6  /  

   ID NAME         FIRST_BYTES                      LOB_SIZE  
----- ------------ -------------------------------- ----------  
    1 Koala.jpg    /9j/4AAQSkZJRgABAgEAYABgAAD/7g      1068504  

SQL>  
SQL> declare  
  2   
  3    x_doc       xmltype;  
  4    domdoc      dbms_xmldom.DOMDocument;  
  5    domnode     dbms_xmldom.DOMNode;  
  6    domtext     dbms_xmldom.DOMText;  
  7    ostream     sys.utl_BinaryOutputStream;  
  8    chunksize   pls_integer;  
  9    offset      pls_integer := 1;  
10    buf         raw(32767);  
11    image       blob;  
12   
13  begin  
14   
15    select xmlelement("image",  
16             xmlelement("id", t.id)  
17           , xmlelement("name", t.name)  
18           , xmlelement("content")  
19           )  
20         , t.content  
21    into x_doc  
22       , image  
23    from images t  
24    where t.id = 1 ;  
25   
26    domdoc := dbms_xmldom.newDOMDocument(x_doc);  
27   
28    -- get /image/content node so that we can append a text node and stream the BLOB to it :  
29    domnode := dbms_xslprocessor.selectSingleNode(dbms_xmldom.makeNode(domdoc), '/image/content');  
30    domtext := dbms_xmldom.createTextNode(domdoc, null);  
31    domnode := dbms_xmldom.appendChild(domnode, dbms_xmldom.makeNode(domtext));  
32   
33    ostream := DBMS_XMLDOM.setNodeValueAsBinaryStream(domnode);  
34    chunksize := dbms_lob.getchunksize(image);  
35   
36    loop  
37      begin  
38        -- read BLOB in chunk of <chunksize> :  
39        dbms_lob.read(image, chunksize, offset, buf);  
40      exception  
41        when no_data_found then  
42          exit;  
43      end;  
44      -- write chunk to DOM node :  
45      ostream.write(buf, chunksize);  
46      offset := offset + chunksize;  
47    end loop;  
48   
49    ostream.flush();  
50    ostream.close();  
51   
52    dbms_xmldom.writeToFile(domdoc, 'XML_DIR/image.xml');  
53    dbms_xmldom.freeDocument(domdoc);  
54   
55  end;  
56  /  

PL/SQL procedure successfully completed  

Output in image.xml :

<image>  
  <id>1</id>  
  <name>Koala.jpg</name>  
  <content>/9j/4AAQSkZJRgABAgEAYABgAAD/7gAOQWRvYmUAZAAAAAAB/+EUI0V4aWYAAE1NACoAAAAIAAcB  
MgACAAAAFAAAAGIBOwACAAAABwAAAHZHRgADAAAAAQAEAABHSQADAAAAAQA/AACcnQABAAAADgAA  
AADqHAAHAAAH9AAAAACHaQAEAAAAAQAAAH0AAADnMjAwOTowMzoxMiAxMzo0ODoyOABDb3JiaXMA  
...  
aWpkRY6dqaJY42qFjs8ZZ5nlkjZpmJUiJ1+ijn3dVeTtb4B1R5NJVaEkn/V8+u4Xijpp6SFlimp6  
qNHgjlVIgrtLCyxll1NpaT6An6W92mA8HwkyadMeEZJjEDmvE/t/1f5uv//Z</content>  
</image>