oracle操作xml格式数据

新建一张用来测试的表

-- Create table
create table XMLTEST
(
  id      NUMBER,
  content VARCHAR2(4000)
);

往表中插入数据

insert into XMLTEST (id, content)
values (1, '<root><app><id>1</id><name>张三</name><age>18</age></app><app><id>2</id><name>李四</name><age>20</age></app><app><id>3</id><name>王五</name><age>30</age></app></root>');

insert into XMLTEST (id, content)
values (2, '<root><app><id>1</id><name>张三</name><age>18</age></app></root>');

字段CONTENT的值为字符串格式的xml数据，

ID为1的记录结构如下：

 

ID为2的记录结构如下：

 

下面使用sql对xml数据进行查询：

示例一：将字符串转为xml格式

select xmltype(CONTENT)  vxml from xmltest;

 

 示例二：返回一个节点下的所有值

返回id为1的记录，所有的name的值

--extract 返回一个节点下的所有值
select  extract(xmltype(CONTENT), 'root/app/name') vxml from xmltest where id = 1;

 

示例三：将xml数据转为表数据格式

当xml只有一个节点时（ID=2的记录）

--extractvalue只能返回一个节点的一个值  
select x.id tblid,
extractvalue(xmltype(x.CONTENT),'root/app/id') as id,
extractvalue(xmltype(x.CONTENT),'root/app/name') as name,
extractvalue(xmltype(x.CONTENT),'root/app/age') as age
from xmltest x where x.id = 2;

 

当xml有多个节点时（ID=1的记录）

--table和XMLSequence 将xml节点数据转为表数据格式
select  x.id tblid,
extractValue(value(newval),'/app/id') id,
extractValue(value(newval),'/app/name') name,
extractValue(value(newval),'/app/age') name
from xmltest x,
table(XMLSequence( extract(xmltype(x.CONTENT), 'root/app') )) newval
where x.id = 1;