Adding CLOB values to XML as CDATA

Adding large CLOB values into an XML structure using XMLDOM package in PL/SQL is challenging because XMLDOM methods do not accept CLOB data type.

The solution is to divide large CLOB values into multiple smaller chunks. The size of each chunk we create is 8000. We are not using the maximum length allowed for VARCHAR2 data type (32767 bytes) because we have a multi-lingual database and some characters are stored in 4 bytes.
We are using CDATA nodes to store these chunks because our CLOB values generally contain data as XML and we don’t want to deal with escape characters.

The following function takes a CLOB and an XMLDOM.domNode as parameters and returns an XMLDOM.domNode which contains the CLOB value. If the length of the CLOB is bigger than the defined maximum length (8000 in our case), the method divides the CLOB into several chunks prior including it to the domNode.

function addValueToXMLNode(in_parent_node xmldom.domNode, in_value_cl CLOB)
return xmldom.domNode
is
  v_offset_nr         number;
  v_left_nr           number;
  v_partNo_nr         number;
  v_buffer_tx         varchar2(32767);
  v_maxdatalength_nr  number := 8000;
  
  v_parent_doc          xmldom.domDocument;
  v_part_node           xmldom.domNode;
  v_bufferedValue_cdata xmldom.DOMCDATASection;
  v_temp_node           xmldom.domNode;
  v_value_node          xmldom.domNode;
  
begin
  -- get the owner document of the parent node
  v_parent_doc := xmldom.getownerdocument(in_parent_node);
    
  -- create a new node named "value" and add it to the parent node 
  v_value_node := xmldom.makeNode(xmldom.createelement(v_parent_doc, 'value'));
  v_temp_node:= xmldom.appendchild(in_parent_node, v_value_node);

  -- get the length of CLOB value
  v_left_nr := dbms_lob.getlength(in_value_cl);

  -- if CLOB is bigger then the defined maximum value, 
  -- divide it, otherwise add it directly to the parent node
  if v_left_nr > v_maxdatalength_nr then
    -- set multi part attribute to Yes
    xmldom.setattribute(xmldom.makeelement(v_value_node), 'multiPart', 'Y');
    
    v_offset_nr := 1;
    v_partNo_nr := 1;
    loop
      exit when v_left_nr <= 0;
    
      if v_left_nr > v_maxdatalength_nr then
        v_buffer_tx := dbms_lob.substr(in_value_cl, v_maxdatalength_nr, v_offset_nr);
        v_left_nr := v_left_nr - v_maxdatalength_nr;
        v_offset_nr := v_offset_nr + v_maxdatalength_nr;
      else
        v_buffer_tx := dbms_lob.substr(in_value_cl, v_left_nr, v_offset_nr);
        v_left_nr := 0;
      end if;
      -- create a node for each chunk and give it a number
      v_part_node := xmldom.makenode(xmldom.createelement(v_parent_doc,'part'));
      xmldom.setattribute(xmldom.makeelement(v_part_node), 'no', v_partNo_nr);
      v_partNo_nr := v_partNo_nr + 1;
      
      -- create a CDATA node with buffered value and add it to the v_part_node
      v_bufferedValue_cdata:=xmldom.createcdatasection(v_parent_doc,v_buffer_tx);
      v_temp_node := xmldom.appendchild(v_part_node, xmldom.makeNode(v_bufferedValue_cdata));
      -- add v_part_node to the value node
      v_temp_node := xmldom.appendchild(v_value_node, v_part_node);
    end loop;

  else
    -- set multi part attribute to No 
    xmldom.setattribute(xmldom.makeelement(v_value_node), 'multiPart', 'N');
    -- create a CDATA node with buffered value and add it to the value node directly
    v_bufferedValue_cdata:=xmldom.createcdatasection(v_parent_doc, to_char(in_value_cl));
    v_temp_node := xmldom.appendchild(v_value_node, xmldom.makeNode(v_bufferedValue_cdata));
  
  end if;
  
  return in_parent_node;
end;

As the next step, we need to extract the chunks from the XML and rebuild the CLOB..
The following function accepts an XMLDOM.domElement as its input parameter, extracts the CLOB inside the domElement and returns it.

function readValueFromXMLNode(in_parent_node  xmldom.domNode)
return clob
is
  v_value_cl            clob;
  v_multiPart_yn        varchar2(1);
  
  v_parent_doc          xmldom.domDocument;
  v_part_node           xmldom.domNode;
  v_children_nodeList   xmldom.domNodeList;
  v_child_node          xmldom.domNode;
  v_value_node          xmldom.domNode;
begin

  -- get owner document
  v_parent_doc := xmldom.getownerdocument(in_parent_node);
  
  -- get value node
  v_value_node := xmldom.getfirstchild(in_parent_node);
  -- check if this is multipart value or not
  v_multiPart_yn := xmldom.getAttribute(xmldom.makeelement(v_value_node), bdf_constant.attrmultipart);
  if v_multiPart_yn = 'Y' then
    -- get the list of the nodes that have the parts
    v_children_nodeList := xmldom.getchildnodes(v_value_node);
    -- empty clob value
    v_value_cl := '';
    -- read all values and append to clob
    for index_nr in 0..xmldom.getlength(v_children_nodeList)-1 loop
      v_child_node := xmldom.item(v_children_nodeList, index_nr);
      
      v_part_node := xmldom.getfirstchild(v_child_node);
      v_value_cl := v_value_cl || xmldom.getnodevalue(v_part_node);
    end loop;
  
  else
   -- if it is not multi-part value, get the CDATA node and read the value
    v_child_node := xmldom.getfirstchild(v_value_node);
    v_value_cl := xmldom.getnodevalue(v_child_node);
  end if;
  
  return v_value_cl;
  
end;

Here is sample code which calls both functions:

declare
  v_value_cl      clob;
  v_returned_cl   clob;
  v_parent_doc    xmldom.domDocument;
  v_parent_node   xmldom.domNode;
begin
  -- create new xml document
  v_parent_doc := xmldom.newdomdocument('');
  -- get parent noode
  v_parent_node := xmldom.makenode(xmldom.getdocumentelement(v_parent_doc));
  -- create sample clob value
  v_value_cl :=
'<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://sinop/fs_smt/bdf_ide_dev.xsd">
  <mainframe title="Formspider" width="500" height="500" maximizeOnStartUp="Y" icon="/icons/mainframe/fs.png" resizable="Y" close="Y" minimize="Y" maximize="Y">
    <events>
      <keyEvents></keyEvents>
    </events>
  </mainframe>
</root>';
  -- add value to XML
  v_parent_node := addValueToXMLNode(v_parent_node, v_value_cl);
  -- read value from XML
  v_returned_cl := readValueFromXMLNode(v_parent_node);
  -- compare CLOB values
  if dbms_lob.compare(v_value_cl,v_returned_cl) = 0 then
    dbms_output.put_line('successful');
  end if;
  
end;

Samet Basaran

Comments

Popular posts from this blog

Powerful Free Webinar Network for Oracle Developers

Monitoring Oracle Database with Zabbix