--------------------------方法一-------------------------------------
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>
输出结果