1、XMLType
drop table xmlExample;
create table xmlExample
(
ID varchar2(32),name varchar2(200),data XMLType
);
insert into XMLExample values(sys_guid(),'Exec','<students><student><number>000001</number><name>黎明</name></student><student><number>000002</number><name>赵怡春</name></student></students>');
insert into XMLExample values(sys_guid(),'Exec',xmlType.createXML('<students><student id="1"><number>000001</number><name>黎明</name></student><student id="2"><number>000002</number><name>赵怡春</name></student></students>'));
select * from xmlExample;
2、关系和XML转换
从XML中查询得到关系结果(如果节点不存在,则检索出NULL,不会报错)
查询(别名不可少)
select i.data.extract('//student/name').getStringVal() as stuname, id from xmlExample i
select i.data.extract('//student/name/text()').getStringVal() as stuname, id from xmlExample i
select i.data.extract('//student[@id>1]/name').getStringVal() as stuname, id from xmlExample i
select extractvalue(data,'/students/student[@id=1]/name') as name from xmlExample
(只能返回一个记录)
select extractValue(value(i),'/name') xx from xmlexample x, table(XMLSequence(extract(x.data,'/students/student/name'))) i;
从关系表得到XML
select xmlforest(name,height) from student
[此贴子已经被作者于2010-12-11 20:25:14编辑过]