I need using the package dbms_xmldom modify the XML as follows:
<MODIFICATIONS nums="" chars="" del_tags=""/>where
that is, for example, should be so: XML input
<? xml version = "1 .0" encoding ="windows-1251" standalone ="yes"?>
<ROOT a="000" b="aacckf" d="75" f="69">
<SEAL c="12"/>
</ROOT>
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 :
<? xml version = "1 .0" encoding ="windows-1251" standalone ="yes"?>
<ROOT b="AACCKF">
<MODIFICATIONS nums="4" chars="1" del_tags="1"/>
</ROOT>
I use it
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, '<? xml version = "1 .0" encoding ="windows-1251" standalone ="yes"?>
<ROOT a="000" b="aacckf" d="75" f="69">
<SEAL c="12"/>
</ROOT>');
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,',') > 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;
This I found and modified and stuck on this
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;
I have written so please correct if something can be improved or corrected.
DECLARE
g_doc dbms_xmldom.DOMDocument; -- basic DOM-document
g_node dbms_xmldom.DOMNode;
g_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 manipulating the attributes of a node
procedure manipulation_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
l_elem dbms_xmldom.DOMElement;
l_tmp_Attr dbms_xmldom.DOMAttr;
l_sattrs varchar2 (2000); -- Name of the attribute
l_vattrs varchar2 (2000); -- the value of the attribute
l_nval varchar2 (2000); -- the value of the node
l_nnam varchar2 (2000); -- the host name
l_nattrs_len integer;
l_nattrs_del integer default 0;
begin
-- Get the attributes of a node
l_elem: = dbms_xmldom.makeElement (p_node);
l_nattrs: = dbms_xmldom.GetAttributes (p_node);
-- /*
-- Manipulation with attribute values
if not dbms_xmldom.isNull (l_nattrs) then
l_nattrs_len: = dbms_xmldom.GetLength (l_nattrs) - 1;
while (l_nattrs_len + 1! = l_nattrs_del) /* and (l_nattrs_len> = 0) */
loop
l_node: = dbms_xmldom.item (l_nattrs, l_nattrs_del);
l_sattrs: = dbms_xmldom.GetNodeName (l_node);
l_vattrs: = dbms_xmldom.GetNodeValue (l_node);
-- Number - delete
if (isnumber (l_vattrs)) then
l_tmp_Attr: = dbms_xmldom.getAttributeNode (l_elem, l_sattrs);
l_tmp_Attr: = dbms_xmldom.removeAttributeNode (l_elem,
l_tmp_Attr);
l_nattrs_len: = l_nattrs_len - 1;
g_cnum: = g_cnum + 1;
-- Text - uppercase
else
dbms_xmldom.setNodeValue (l_node, upper (l_vattrs));
g_cchar: = g_cchar + 1;
l_nattrs_del: = l_nattrs_del + 1;
end if;
end loop;
end if;
-- */
end;
-- A recursive procedure for constructing the DOM tree of the document
procedure tree_manipulation_node_element (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_next_node dbms_xmldom.DOMNode; -- the current node 1
l_del_node dbms_xmldom.DOMNode; -- delete the current node
l_nval varchar2 (2000); -- the value of the node
l_nnam varchar2 (2000); -- name node
l_nlist_len number;
begin
-- Open the node description
dbms_output.put_line ('start:' | | dbms_xmldom.getNodeName (p_node));
-- Manipulation of the attributes node
manipulation_node_attributes (p_node);
-- Repeat the steps you
if dbms_xmldom.hasChildNodes (p_node) then
l_node: = dbms_xmldom.getFirstChild (p_node);
l_next_node: = l_node;
while not dbms_xmldom.isNull (l_next_node) loop
if (dbms_xmldom.getNodeType (l_node) = 1) then
tree_manipulation_node_element (l_node);
end if;
l_next_node: = dbms_xmldom.getNextSibling (l_node);
if ((not dbms_xmldom.hasAttributes (l_node)) and
(Dbms_xmldom.getNodeType (l_node) = 1) and dbms_xmldom.getNodeName (dbms_xmldom.getParentNode (l_node))! = '#document') then
if ((not dbms_xmldom.hasChildNodes (l_node))) or (dbms_xmldom.hasChildNodes (l_node) and
(Dbms_xmldom.getNodeType (dbms_xmldom.getFirstChild (l_node))! = 1)) then
dbms_output.put_line ('deleted: ---->' | |
dbms_xmldom.getNodeName (l_node));
l_del_node: = dbms_xmldom.removeChild (dbms_xmldom.getParentNode (l_node),
l_node);
g_cdeltags: = g_cdeltags + 1;
end if;
end if;
l_node: = l_next_node;
end loop;
end if;
-- Close the node description
dbms_output.put_line ('Endof:' | | DBMS_XMLDOM.getNodeName (p_node));
end;
BEGIN
for cur in (select num_id, before_xml
from xmldom_sidor
/* Where num_id = 4 */
order by num_id) loop
select cur.before_xml into g_clob from dual;
g_cnum: = 0;
g_cchar: = 0;
g_cdeltags: = 0;
-- Building a tree
g_doc: = dbms_xmldom.newDOMDocument (g_clob);
g_node: = dbms_xmldom.makeNode (g_doc);
tree_manipulation_node_element (g_node);
g_nlist: = dbms_xmldom.getChildNodes (g_node);
g_node: = dbms_xmldom.item (g_nlist, 0);
-- Adding a new element and attribute
g_new_el: = dbms_xmldom.createElement (g_doc, 'MODIFICATIONS');
dbms_xmldom.setAttribute (g_new_el, 'nums', g_cnum);
dbms_xmldom.setAttribute (g_new_el, 'chars', g_cchar);
dbms_xmldom.setAttribute (g_new_el, 'del_tags', g_cdeltags);
g_node: = dbms_xmldom.appendChild (g_node,
dbms_xmldom.makeNode (g_new_el));
dbms_xmldom.writeToClob (g_doc, g_clob);
update xmldom_sidor x
set x.after_xml = g_clob
where x.num_id = cur.num_id;
dbms_xmldom.freeDocument (g_doc);
end loop;
END;