ORACLE parsing XML string-reproduced

1.xml string

/*

<orderlist>
<order>
<orderid>1</orderid>
<ordernumber>857544544</ordernumber>
<orderprice>54</orderprice>
</order>

<order>
<orderid>2</orderid>
<ordernumber>858544544</ordernumber>
<orderprice>63</orderprice>
</order>

<order>
<orderid>3</orderid>
<ordernumber>454854555</ordernumber>
<orderprice>781</orderprice>
</order>
</orderlist>

*/

2.oracle Stored Procedures

CREATE OR REPLACE PROCEDURE p_xmlparse (p_xml in CLOB,
R_cur out Sys_refcursor)
As
/***
XML format <?xml version= "1.0" encoding= "Utf-8"?>
<material id= "101" >
<attribute0>value0</attribute0>
<attribute1>value1</attribute1>
<attribute2>value2</attribute2>
</material>
***/
--XML Parser
Xmlpar Xmlparser.parser: = Xmlparser.newparser;
--DOM Document object
Doc Xmldom.domdocument;
Materialnodes xmldom.domnodelist;
Materialid VARCHAR2 (50);
Chilnodes xmldom.domnodelist;
Tempnode Xmldom.domnode;
Temparrmap Xmldom.domnamednodemap;
--The following variables are used to get the value of the XML node
V_attribute VARCHAR2 (50);
V_value VARCHAR2 (50);
TMP INTEGER;
L_sql VARCHAR2 (32767): = ' SELECT ';
BEGIN
Xmlparser.parseclob (Xmlpar, p_xml);
Doc: = Xmlparser.getdocument (Xmlpar);
--Releasing the parser instance
Xmlparser.freeparser (Xmlpar);
Materialnodes: = Xmldom.getelementsbytagname (doc, ' material ');
Tempnode: = Xmldom.item (materialnodes, 0);
--Gets the id attribute value of the root element
Materialid: = Xmldom.getattribute (Xmldom.getdocumentelement (DOC), ' id ');
--All properties
Temparrmap: = Xmldom.getattributes (Tempnode);
--Get the value of the child element
Chilnodes: = Xmldom.getchildnodes (Tempnode);
TMP: = Xmldom.getlength (chilnodes);
L_sql: = L_sql | | Materialid | | ' As Materialid ';
For i in 0.. Tmp-1
LOOP
V_attribute: = Xmldom.getnodename (Xmldom.item (Chilnodes, i));
V_value: =
Xmldom.getnodevalue (
Xmldom.getfirstchild (Xmldom.item (Chilnodes, i));
L_sql: = L_sql | | ', ' ' | | V_value | | ' As ' | | V_attribute;
--Dbms_output.put_line (materialid| | ' ' | | v_attribute| | ' ' | | V_value);
END LOOP;
L_sql: = L_sql | | ' From dual ';
--Dbms_output.put_line (L_sql);
OPEN r_cur for L_sql;
--Releasing the Document object
Xmldom.freedocument (DOC);
EXCEPTION
When OTHERS
Then
Dbms_output.put_line (SQLERRM);
END P_xmlparse;

3. Implementation results

Source Document Address: http://blog.csdn.net/wzy0623/article/details/8245062

ORACLE parsing XML string-reproduced