---PLSQL解析XML-
--实例1
declare
v_xml xmltype;
cursor c_parse(v_xml xmltype) is
select trim(extractValue(value(a),'Corporation/Name')) cor_name,
trim(extractValue(value(a),'Corporation/Name/Addr')) cor_addr
from table(xmlsequence(extract(v_xml,'Root/Corporation'))) a;
begin
select xmltype('<?xml version="1.0" encoding="GBK"?>'
|| '<Root>'
|| ' <Corporation>'
|| ' <Name> suqiang </Name>'
|| ' <Addr>No.628 Zhangyang Road</Addr>'
|| ' </Corporation>'
|| '</Root>')
into v_xml
from dual;
for v_cor in c_parse(v_xml) loop
dbms_output.put_line('Corporation name:' || v_cor.cor_name || ';');
dbms_output.put_line('Corporation Addr:' || v_cor.cor_addr || ';');
end loop;
end;
------实例2
declare
v_xml xmltype;
cursor c_parse(v_xml xmltype) is
select trim(extractValue(value(a),'Corporation/Name')) cor_name,
extract(value(a),'Corporation/Persons/Person') cor_person
from table(xmlsequence(extract(v_xml,'Root/Corporation'))) a;
cursor c_person(v_persons xmltype) is
select trim(extractValue(value(a),'Person/Name')) realname,
trim(extractValue(value(a),'Person/Tel')) tel
from table(xmlsequence(extract(v_persons,'Person'))) a;
begin
select xmltype('<?xml version="1.0" encoding="GBK"?>'
|| '<Root>'
|| ' <Corporation>'
|| ' <Name>suqiang</Name>'
|| ' <Persons>'
|| ' <Person id="zhangsan">'
|| ' <Name>张三</Name>'
|| ' <Tel>12345</Tel>'
|| ' </Person>'
|| ' <Person id="lisi">'
|| ' <Name>李四</Name>'
|| ' <Tel>23456</Tel>'
|| ' </Person>'
|| ' </Persons>'
|| ' </Corporation>'
|| '</Root>')
into v_xml
from dual;
for v_cor in c_parse(v_xml) loop
dbms_output.put_line('Corporation name:' || v_cor.cor_name || ';');
for v_person in c_person(v_cor.cor_person) loop
dbms_output.put(v_person.realname || ':');
dbms_output
PLSQL解析XML
最新推荐文章于 2023-07-10 14:26:45 发布
本文详细介绍了如何在PLSQL中解析XML,通过四个实例展示了从XML中提取不同层级数据的方法,包括公司名称、联系人信息以及部门信息。示例代码清晰地演示了如何使用extractValue和xmlsequence等函数来解析XML文档。
摘要由CSDN通过智能技术生成