oracle log.xml分析,ORACLE 解析xml字符串-转载的

1.xml字符串

/*

1

857544544

54

2

858544544

63

3

454854555

781

*/

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"?>

value0

value1

value2

***/

-- 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

原文：http://www.cnblogs.com/iyoume2008/p/4783101.html