--------------------------方法一-------------------------------------
1.xml字符串
/*
<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存储过程
CREATE OR REPLACE PROCEDURE p_xmlparse (p_xml IN CLOB, r_cur OUT SYS_REFCURSOR) AS /*** xml格式<?xml version="1.0" encoding="utf-8"?> <material id="101"> <attribute0>value0</attribute0> <attribute1>value1</attribute1> <attribute2>value2</attribute2> </material> ***/ -- xml解析器 xmlpar xmlparser.parser := xmlparser.newparser; -- dom文档对象 doc xmldom.domdocument; materialnodes xmldom.domnodelist; materialid VARCHAR2 (50); chilnodes xmldom.domnodelist; tempnode xmldom.domnode; temparrmap xmldom.domnamednodemap; -- 以下变量用于获取xml节点的值 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); -- 释放解析器实例 xmlparser.freeparser (xmlpar); materialnodes := xmldom.getelementsbytagname (doc, 'material'); tempnode := xmldom.item (materialnodes, 0); -- 获取根元素的ID属性值 materialid := xmldom.getattribute (xmldom.getdocumentelement (doc), 'id'); -- 所有属性 temparrmap := xmldom.getattributes (tempnode); -- 获取子元素的值 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; -- 释放文档对象 xmldom.freedocument (doc); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLERRM); END p_xmlparse;
3.执行结果
源文档地址：http://blog.csdn.net/wzy0623/article/details/8245062
--------------------------方法二-------------------------------------
SELECT * FROM XMLTABLE('$B/DEAL_BASIC/USER_DEAL_INFO' PASSING XMLTYPE('<?xml version="1.0" encoding="gb2312" ?>&xml') AS B COLUMNS USER_DEAL_ID VARCHAR2(50) PATH '/USER_DEAL_INFO/USER_DEAL_ID', DEAL_INURE_TIME VARCHAR2(50) PATH '/USER_DEAL_INFO/DEAL_INURE_TIME', DEAL_EXPIRE_TIME VARCHAR2(50) PATH '/USER_DEAL_INFO/DEAL_EXPIRE_TIME', DEAL_CREATE_TIME VARCHAR2(50) PATH '/USER_DEAL_INFO/DEAL_CREATE_TIME')
xml字符串以传入参数的方式 输入
<DEAL_BASIC> <USER_DEAL_INFO> <USER_DEAL_ID>1000100001</USER_DEAL_ID> <DEAL_INURE_TIME>20081130</DEAL_INURE_TIME> <DEAL_EXPIRE_TIME>30000101</DEAL_EXPIRE_TIME> <DEAL_CREATE_TIME>20081130</DEAL_CREATE_TIME> </USER_DEAL_INFO> <USER_DEAL_INFO> <USER_DEAL_ID>1000100002</USER_DEAL_ID> <DEAL_INURE_TIME>20081131</DEAL_INURE_TIME> <DEAL_EXPIRE_TIME>30000102</DEAL_EXPIRE_TIME> <DEAL_CREATE_TIME>20081131</DEAL_CREATE_TIME> </USER_DEAL_INFO> <USER_DEAL_INFO> <USER_DEAL_ID>1000100002</USER_DEAL_ID> <DEAL_INURE_TIME>20081131</DEAL_INURE_TIME> <DEAL_EXPIRE_TIME>30000102</DEAL_EXPIRE_TIME> <DEAL_CREATE_TIME>20081131</DEAL_CREATE_TIME> </USER_DEAL_INFO></DEAL_BASIC>
输出结果
转载于:https://www.cnblogs.com/iyoume2008/p/4783101.html
