17,086
社区成员
发帖
与我相关
我的任务
分享
select t.content.extract('//Sheets/Page/@num1').getNumberVal() as val from project t;
select t.content.extract('//Sheets/Page/@num1').getNumberVal() as val from project t;
改为
select t.content.extract('//Sheets/Page/Detail/@num1').getNumberVal() as val from project t;
-- Create table
create table project
(
id varchar2(50),
content sys.xmltype
);
insert into project (id, content) values ('101', sys.xmltype.createXML('<Sheets><Page SheetIndex="1"><Detail id="1" num1="100"/><Detail id="2" num1="120"/><Detail id="3" num1="110"/><Detail id="4" num1="120"/></Page></Sheets>'));
COMMIT;
--实现此步可参考
select length('<Detail s1="1"/><Detail s1="2"/><Detail s1="3"/>') -
length(replace('<Detail s1="1"/><Detail s1="2"/><Detail s1="3"/>',
'<Detail',
'Detail')) iCount
from dual;
第二步:
select t.content.extract('//Sheets/Page/Detail[1]/@num1').getNumberVal() as val from project t;
select t.content.extract('//Sheets/Page/Detail[2]/@num1').getNumberVal() as val from project t;
--逐条查询出结果值,直到第一步求出的记录条数。再进行合计。