Parsing xml strings using stored procedures in oracle

Oracle parses xml strings using stored procedures. Background: There are two data tables in the database: t_xml and t_parse. The former is to store the main nodes in the xml document into the para_name field as strings, the latter is used to parse the xml string in the para_name field and extract the child node for storage. For example, if the xml string in www.2cto.com is <item> <cpu_name> name1 </cpu_name> <value> 80% </value> </item>, in the t_parset data table, name1 and 80% are extracted for storage. The t_xml table structure is <id, para_name>, and the t_parse table structure is <id, name, value>, the xml string structure is <item> <cpu_name> name1 </cpu_name> <value> 80% </value> </item>. Stored procedure: create or replace procedure p_parse (ID in varchar2) is www.2cto.com -- create an xml Parser instance xmlparser. parser xmlPar xmlparser. parser: = xmlparser. newParser; -- defines the DOM document xDoc xmldom. DOMDocument; -- defines the variable lenItme integer of the number of item subnodes; -- defines the node list and stores itemNodes xmldom of item nodes. DOMNodeList; -- defines the node list and stores item subnodes childNodes xmldom. DOMNodeList; -- defines a node and stores the itemNode xmldom of a single item node. DOMNode; -- defines attribute variables and stores node attributes itemArrMap xmldom. DOMNamedNodeMap ;-- Define other variables and store the value of the subnode name varchar (100); value varchar (100); -- Define the clob variable and store the xml string para_namexmlStr clob; begin for cur in (select t. para_name, t. id from t_xml t where t. id = ID) -- query data from the t_xml table. id is not a unique identifier. An id can identify multiple data entries. Therefore, use loop -- to obtain the xml string xmlStr: = cur. para_name; xmlPar: = xmlparser. newParser; -- parses xml strings in xmlStr and stores them in xmlparser. parseClob (xmlPar, xmlStr); -- transfers data from xmlPar to the dom file. xDoc: = xmlparser. getDoc Ument (xmlPar); xmlparser. freeParser (xmlPar); -- release the parser instance -- get all item nodes itemNodes: = xmldom. getElementsByTagName (xDoc, 'item'); -- get the number of item nodes lenItme: = xmldom. getLength (itemNodes); -- traverses all item nodes for I in 0 .. lenItme-1 loop begin -- get the I-th item node itemNode: = xmldom in the node list. item (itemNodes, I); -- get all the subnodes of the item node I childNodes: = xmldom. getChildNodes (itemNode); -- get the Value name: = xmldom of all child nodes. getNodeValue (xmldom. getFirstChild (Xmldom. item (childNodes, 0); value: = xmldom. getNodeValue (xmldom. getFirstChild (xmldom. item (childNodes, 1); -- store the values of sub-nodes name1 and 80% in the t_parse table insert into t_parse values (ID, name, value); commit; end; end loop; -- whether the dom document xmldom. freeDocument (xDoc); -- EXCEPTION and error handling exception when others then DBMS_output.PUT_LINE (SQLERRM); end p_parse; after the storage process is created, you can test it. Right-click the name of the stored procedure, click test in the shortcut menu to perform the test. During the test, you must manually enter the id value and perform the test step by step.