(原創)從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

示例如下：

  extractvalue(VALUE(t),'/bag/end_org_code') end_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,

  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()函數

使用這兩個函數時必須安裝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的從表路徑。