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

ゝ一纸荒年。 2023-01-18 04:25 132阅读 0赞

1.xml字符串

/\*

857544544

858544544

454854555

781

\*/

2.oracle存储过程

CREATE OR REPLACE PROCEDURE p\_xmlparse (p\_xml   IN     CLOB,

r\_cur      OUT SYS\_REFCURSOR)

/\*\*\*

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.执行结果

![L8NffsG0DF2zTgrRGdGsbP5r+n1Mr4LXiU58XUZ7kqz19zbt8Ait6mAQPAJ9GAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWCBr6+vfwBU7Xr1M46BKgAAAABJRU5ErkJggg==][L8NffsG0DF2zTgrRGdGsbP5r_n1Mr4LXiU58XUZ7kqz19zbt8Ait6mAQPAJ9GAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWCBr6_vfwBU7Xr1M46BKgAAAABJRU5ErkJggg]

源文档地址：http://blog.csdn.net/wzy0623/article/details/8245062

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

[L8NffsG0DF2zTgrRGdGsbP5r_n1Mr4LXiU58XUZ7kqz19zbt8Ait6mAQPAJ9GAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWABDRgAFtCAAWCBr6_vfwBU7Xr1M46BKgAAAABJRU5ErkJggg]: