title: Delete nodes and attributes in xml using dbms_xmldom in oracle 9i
body: <p>I need using the package dbms_xmldom modify the XML as follows:</p>
  
  <ul>
  <li>If the value of the attribute only numbers, then this attribute is removed</li>
  <li>If not only the numbers, the drive to upper case</li>
  <li>If after modifications or initially has no tag attributes, then it should be removed </li>
  <li>In the root tag must add tag <code>&lt;MODIFICATIONS nums="" chars="" del_tags=""/&gt;</code></li>
  </ul>
  
  <p>where</p>
  
  <ul>
  <li><strong>nums</strong> specify the number of deleted attributes with numbers</li>
  <li><strong>chars</strong> to specify the number of attributes with non-digits, which are given to upper case</li>
  <li><strong>del_tags</strong> - the number of remote tag</li>
  </ul>
  
  <p>that is, for example, should be so:
  XML input</p>
  
  <pre><code>&lt;? xml version = "1 .0" encoding ="windows-1251" standalone ="yes"?&gt;
  &lt;ROOT a="000" b="aacckf" d="75" f="69"&gt;
      &lt;SEAL c="12"/&gt;
  &lt;/ROOT&gt;
  </code></pre>
  
  <p>we have to remove the attributes a = "000", d="75", f="69", c = "12" and attribute b = "aacckf" changed to b = "AACCKF" and remove the tag :</p>
  
  <pre><code>&lt;? xml version = "1 .0" encoding ="windows-1251" standalone ="yes"?&gt;
      &lt;ROOT b="AACCKF"&gt;
          &lt;MODIFICATIONS nums="4" chars="1" del_tags="1"/&gt;
      &lt;/ROOT&gt;
  </code></pre>
  
  <p><strong>I use it</strong></p>
  
  <pre><code>drop table xmldom_table;
  
  create table xmldom_table(
  num_id integer,
  before_xml clob,
  after_xml clob
  );
  
  insert into xmldom_table
  ( num_id, before_xml )
  values
  (1, '&lt;? xml version = "1 .0" encoding ="windows-1251" standalone ="yes"?&gt;
      &lt;ROOT a="000" b="aacckf" d="75" f="69"&gt;
          &lt;SEAL c="12"/&gt;
      &lt;/ROOT&gt;');
  
  create or replace function isnumber( p_string in varchar2 ) return boolean
    is
            not_number exception;
            pragma exception_init( not_number, -6502 );
            l_number number;
            l_return boolean := FALSE;
    begin
            if (instr(p_string,',') &gt; 0 )
            then
                    l_number := to_number( p_string, '999g999g999g999g999g999d999999999999999999' );
            else
                    l_number := to_number( p_string );
            end if;
            return TRUE;
    exception
            when not_number
            then
                    return FALSE;
    end;
  </code></pre>
  
  <p><strong>This I found and modified and stuck on this</strong></p>
  
  <pre><code>DECLARE
    g_doc dbms_xmldom.DOMDocument; -- basic DOM-document
    g_node dbms_xmldom.DOMNode;
    new_node dbms_xmldom.DOMNode;
    new_el dbms_xmldom.DOMElement;
    g_nlist dbms_xmldom.DOMNodeList; -- list of child nodes
    g_clob clob;
    g_cnum integer default 0;
    g_cchar integer default 0;
    g_cdeltags integer default 0;
  
    -- The procedure for withdrawal of the attributes of a node
    procedure show_node_attributes (p_node in dbms_xmldom.DOMNode)
    is
      l_nattrs dbms_xmldom.DOMNamedNodeMap; -- the list of attributes node
      l_node dbms_xmldom.DOMNode; -- the type of node - the attribute
      elem dbms_xmldom.DOMElement;
      tmp_Attr dbms_xmldom.DOMAttr;
      l_sattrs varchar2 (2000) -- Name of the attribute
      l_vattrs varchar2 (2000) -- the value of the attribute
    begin
      -- Get the attributes of a node
      elem: = dbms_xmldom.makeElement (p_node);
      l_nattrs: = dbms_xmldom.GetAttributes (p_node);
  
      -- Display the names of the attributes and their values ​​on one line
      if not dbms_xmldom.isNull (l_nattrs) then
        dbms_output.put_line ('length' | | dbms_xmldom.GetLength (l_nattrs));
        for i in 0 .. dbms_xmldom.GetLength (l_nattrs) -1 loop
  
          l_node: = dbms_xmldom.item (l_nattrs, i);
          l_sattrs: = dbms_xmldom.GetNodeName (l_node);
          l_vattrs: = dbms_xmldom.GetNodeValue (l_node);
  
          dbms_output.put_line ('before value:' | | i | | '' | | l_sattrs | | '' | | l_vattrs | | '' | | dbms_xmldom.getNodeType (l_node));
          if (isnumber (l_vattrs)) then
             dbms_output.put_line ('value:' | | l_sattrs | | '' | | l_vattrs);
             -- Dbms_xmldom. SetNodeValue (l_node,'');
             tmp_Attr: = dbms_xmldom.getAttributeNode (elem, l_sattrs);
             tmp_Attr: = dbms_xmldom.removeAttributeNode (elem, tmp_Attr);
             dbms_output.put_line ('deleted length' | | dbms_xmldom.GetLength (l_nattrs));
             g_cnum: = g_cnum + 1;
          else
             dbms_xmldom.setNodeValue (l_node, upper (l_vattrs));
             g_cchar: = g_cchar + 1;
          end if;
  
        end loop;
        -- Dbms_output. Put_line ('attrs:' | | l_sattrs);
      END IF;
    END;
  
    -- A recursive procedure for constructing the DOM tree of the document
    procedure recursive_tree (p_node in dbms_xmldom.DOMNode)
    is
      l_nlist dbms_xmldom.DOMNodeList; -- list of child nodes
      l_node dbms_xmldom.DOMNode; -- the current node
      l_nval varchar2 (2000) -- the value of the node
    begin
      -- Open the node description
      -- Dbms_output.put_line ('start:' | | dbms_xmldom.getNodeName (p_node));
  / *
      -- If the value of the node NULL, skip it
      l_nval: = dbms_xmldom.getNodeValue (p_node);
      if l_nval is not null then
          dbms_output.put_line ('value:' | | l_nval);
      end if;
  -- * /
      -- Display the attributes of a node
      show_node_attributes (p_node);
  
      -- For the child node a node considered in the current
      l_nlist: = dbms_xmldom.getChildNodes (p_node);
  
      -- Repeat the steps you
      if not dbms_xmldom.isNull (l_nlist) then
         for i in 0 .. dbms_xmldom.getLength (l_nlist) -1 loop
            l_node: = dbms_xmldom.item (l_nlist, i);
            recursive_tree (l_node);
         end loop;
      end if;
  
      -- Remove the empty sites
      if (1 = 2) then
         g_cdeltags: = g_cdeltags + 1;
      end if;
  
      -- Close the node description
      -- Dbms_output.put_line ('Endof:' | | DBMS_XMLDOM.getNodeName (p_node));
    END;
  
  BEGIN
     -- Building a tree
     select x.before_xml into g_clob from xmldom_table x where x.num_id = 1;
  
     g_doc: = dbms_xmldom.newDOMDocument (g_clob);
     g_node: = dbms_xmldom.makeNode (g_doc);
  
     recursive_tree (g_node);
  
     g_nlist: = dbms_xmldom.getElementsByTagName (g_doc, 'DOC');
     g_node: = dbms_xmldom.item (g_nlist, 0);
  
     new_el: = dbms_xmldom.createElement (g_doc, 'MODIFICATIONS');
     dbms_xmldom.setAttribute (new_el, 'nums', g_cnum);
     dbms_xmldom.setAttribute (new_el, 'chars', g_cchar);
     dbms_xmldom.setAttribute (new_el, 'del_tags', g_cdeltags);
  
     g_node: = dbms_xmldom.appendChild (g_node, dbms_xmldom.makeNode (new_el));
  
     dbms_xmldom.writeToClob (g_doc, g_clob);
  
     update xmldom_table x set x.after_xml = g_clob where x.num_id = 1;
  
  END;
  </code></pre>