17,088
社区成员
发帖
与我相关
我的任务
分享
--1.建立含有xmltype数据类型的表
--声明xmltype型字段用sys.xmltype
create table a (distributiondept sys.xmltype);
--2.向带有xmltype类型的表插入带有数据
--插入用 sys.xmlType.createXML
insert into a (distributiondept) values (sys.xmlType.createXML('<ArrayOfString xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<string>B</string>
<string>C</string>
<string>D</string>
<string>E</string>
</ArrayOfString>') );
--3.查询数据
select i.distributiondept.extract('//ArrayOfString/string/text()').getStringVal() as truename from a i
select i.distributiondept.extract('//ArrayOfString/string[1]/text()').getStringVal() as truename from a i
--4执行更新
update a
set a.distributiondept = updatexml(a.distributiondept, '/ArrayOfString/string[1]/text()', 'BBB');
commit;
--5.查询更新后的数据
select i.distributiondept.extract('//ArrayOfString/string[1]/text()').getStringVal() as truename from a i
UPDATE A
SET A.Distributiondept = UPDATEXML(A.Distributiondept,
'/ArrayOfString/string[1]/text()',
'BBB');