java解析clob的xml数据_(原创)从CLOB字段的XML中提取关系数据研究

从CLOB字段的XML中提取关系数据研究

本文中用以下函数：extract、extractvalue、existsnode、xmlsequence、xmltype、Xmltable、XMLQuery，函数的具体的语法在此不作描述。

在提取数据之前先要把CLOB数据用xmltype函数据转换为XML数据。

1.XML中的数据是单表且只一行数据。

这种情况很简单且速度很快。示例如下：

SELECT

extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/action_code') action_code,

extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/route_id') route_id,

extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/route_actn_code') route_actn_code,

extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/deal_org_code') deal_org_code,

extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/office_code') office_code  ,

to_date(extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/deal_datetime'),'yyyy-mm-dd hh24:mi:ss') deal_datetime,

to_date(extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/act_datetime'),'yyyy-mm-dd hh24:mi:ss') act_datetime,

extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/post_way_code') post_way_code,

extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/route_kind_code') route_kind_code,

extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/bag_count') bag_count,

extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/bag_weight_sum') bag_weight_sum,

extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/flight_info') flight_info ,

to_date(extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/create_time'),'yyyy-mm-dd hh24:mi:ss') create_time

FROM ( SELECT  XMLTYPE(v_msg) v_msg,d_in_time FROM run$log)

WHERE existsnode(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info') =1；

2.XML中的数据是主从表关系。

这种情况下，在抽取子表时必须用xmlsequence函数转换为nest table,否则会报ora-22905。另如果从表的数据量达到千数量级时速度很慢。

2.1使用table()函数xmlsequence

示例如下：

SELECT

extractvalue(VALUE(t),'/bag/end_org_code') end_org_code,

extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/route_id') route_id,

extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/route_actn_code') route_actn_code,

extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/deal_org_code') deal_org_code,

to_date(extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/deal_datetime'),'yyyy-mm-dd hh24:mi:ss') deal_datetimed,

to_date(extractvalue(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/act_datetime'),'yyyy-mm-dd hh24:mi:ss') act_datetime,

extractvalue(VALUE(t),'/bag/bag_action') bag_action,

extractvalue(VALUE(t),'/bag/bag_id') bag_id,

extractvalue(VALUE(t),'/bag/label_strip') label_strip,

extractvalue(VALUE(t),'/bag/start_org_code') start_org_code

FROM ( SELECT  XMLTYPE(v_msg) v_msg,d_in_time FROM run$log) ,

TABLE(xmlsequence(extract(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/bags/bag'))) t；

2.2使用xmltable()和xmlquery()函数

这两个函数的用法不再描述，具体可查官方文档：http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb_xquery.htm

使用这两个函数时必须安装oracle xml db.这种方法比2.1快50%.示例如下：

SELECT extractvalue(v_msg,'/gpdic_xml/bag_detail_infos/bag_detail_info/bag_id') bag_id,

xtab.mail_num ,xtab.mail_action,xtab.mail_remark_code,xtab.mail_other_remark

FROM run$log_test,

Xmltable('for $j in /gpdic_xml/bag_detail_infos/bag_detail_info/mails/mail

return $j'

PASSING v_msg

COLUMNS mail_num VARCHAR2(20) PATH '/mail/mail_num',

mail_action VARCHAR2(1) PATH '/mail/mail_action',

mail_remark_code VARCHAR2(20) PATH '/mail/mail_remark_code',

mail_other_remark VARCHAR2(50) PATH '/mail/mail_other_remark') xtab

WHERE existsnode(v_msg,'/gpdic_xml/bag_detail_infos/bag_detail_info') >0;

补充一下：

以上SQL中的TABLE(xmlsequence(extract(v_msg,'/gpdic_xml/route_detail_infos/route_detail_info/bags/bag'))) t的/gpdic_xml/route_detail_infos/route_detail_info/bags/bag为XML的从表路径。