从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的从表路径。