ORACLE中查看包含XML格式的大字段

由于oracle中大字段查看不方便,如果大字段保存的数据为xml,想要大量查看某些节点的数据的话,则查看不方便,则应先将值得到并插入指定表中,代码如下:

--查看表
select substr(a.request_content,instr(a.request_content,'<DEPTNO>')+8,instr(a.request_content,'</DEPTNO>')-instr(a.request_content,'<DEPTNO>')-8) ,
substr(a.request_content,instr(a.request_content,'<DEPTNAME>')+10,instr(a.request_content,'</DEPTNAME>')-instr(a.request_content,'<DEPTNAME>')-10),
substr(a.request_content,instr(a.request_content,'<OUTORDERID>')+12,instr(a.request_content,'</OUTORDERID>')-instr(a.request_content,'<OUTORDERID>')-12) 
from thorn_interface_log a where a.fun = 'PLATFORM_TMS_SETTMSORDER'

--插入

insert into testdb value (select substr(a.request_content,instr(a.request_content,'<DEPTNO>')+8,instr(a.request_content,'</DEPTNO>')-instr(a.request_content,'<DEPTNO>')-8) ,
substr(a.request_content,instr(a.request_content,'<DEPTNAME>')+10,instr(a.request_content,'</DEPTNAME>')-instr(a.request_content,'<DEPTNAME>')-10),
substr(a.request_content,instr(a.request_content,'<OUTORDERID>')+12,instr(a.request_content,'</OUTORDERID>')-instr(a.request_content,'<OUTORDERID>')-12) 
from thorn_interface_log a where a.fun = 'PLATFORM_TMS_SETTMSORDER')

--查看指定数据
select * from testdb a where a.test3 is not null