oracle.xml解析,oracle解析xml例

-- 例1：

DECLARE

v_xmlstr VARCHAR2(1000);

v_xml    XMLTYPE;

v_title  VARCHAR2(50);

v_language  VARCHAR2(50);

v_body  VARCHAR2(50);

BEGIN

v_xmlstr := '';

v_xml := XMLTYPE(v_xmlstr);    -- 生成XML

SELECT EXTRACTVALUE(VALUE(t),'/html/head/title')

,EXTRACTVALUE(VALUE(t),'/html/head/LANGUAGE')

,EXTRACTVALUE(VALUE(t),'/html/body')

INTO v_title ,v_language ,v_body

FROM TABLE(XMLSEQUENCE(EXTRACT(v_xml, '/html'))) t;    -- 提取XML节点值

dbms_output.put_line(v_title);

dbms_output.put_line(v_language);

dbms_output.put_line(v_body);

END;

/

结果：

This IS title.

JavaScript

I am BODY.

-- 例2：

DECLARE

TYPE v_languageset IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

v_language      v_languageset;

v_xmlstr VARCHAR2(1000);

v_xml    XMLTYPE;

BEGIN

v_xmlstr := '';

v_xml := XMLTYPE(v_xmlstr);

-- 多个值放在表集合中

SELECT EXTRACTVALUE(VALUE(t),'/LANGUAGE')

BULK COLLECT INTO v_language

FROM TABLE(XMLSEQUENCE(EXTRACT(v_xml, '/html/LANGUAGE'))) t;

FOR i IN v_language.first .. v_language.last LOOP

dbms_output.put_line(v_language(i));

END LOOP;

END;

/

结果：

JavaScript

VBScript

JAVA

注意：XML是区分大小写的

-- The End --

11-21 14:11