I have a table with a CLOB column. That column contains XML data. I use the xmldom package to parse, and it works, but I am dissapointed with the performance I get. It takes a minute and eight seconds to parse 11,191 simple XML nodes (after the root nodes, all the nodes are like this, abcd). The database is on Oracle 8.1.7.4 AIX 4.3.

Can anyone suggest ways to improve performance?

The code is below:

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
in_clob_record CLOB;
out_data_type_value VARCHAR2(500);
parser xmlparser.parser;
doc xmldom.domdocument;
doc_node xmldom.domnode;
doc_node_list xmldom.domnodelist;
doc_node_length INTEGER;
doc_counter INTEGER;
node_name VARCHAR2(50);
BEGIN
dbms_output.put_line ('Start: ' || TO_CHAR (SYSDATE, 'HH:MI:SS'));
SELECT clob_record
INTO in_clob_record
FROM mytab
WHERE mytab_uid = 65858;
parser := xmlparser.newParser;
xmlparser.setValidationMode (parser, TRUE);
xmlparser.ParseCLOB (parser, in_clob_record_clob);
doc := xmlparser.getDocument (parser);
doc_node_list := xmldom.getElementsByTagName (doc, '*');
doc_node_length := xmldom.getLength (doc_node_list);
-- Loop through the elements in the XML document.
doc_counter := 0;
WHILE doc_counter < doc_node_length
LOOP
-- Get the node name.
doc_node := xmldom.item (doc_node_list, doc_counter);
doc_counter := doc_counter + 1;
node_name := xmldom.getNodeName (doc_node);
-- Initialize the data type value.
out_data_type_value := '';
-- Get node value for the node. This is the value for the xml_tag.
doc_node := xmldom.getFirstChild (doc_node);
IF (NOT xmldom.IsNull (doc_node)) THEN
IF (xmldom.getNodeType (doc_node) = xmldom.TEXT_NODE) THEN
out_data_type_value := xmldom.getNodeValue (doc_node);
END IF; -- xmldom.getNodeType (doc_node) = xmldom.TEXT_NODE
END IF; -- NOT xmldom.IsNull (doc_node)
END LOOP;
dbms_output.put_line('doc_counter=' || doc_counter);
dbms_output.put_line ('End: ' || TO_CHAR (SYSDATE, 'HH:MI:SS'));
END;
/