小毛驴（xml）试乘记：pl/sql方法

Oracle支持xml。可以用pl/sql加工xml数据。

下面的例子对xml加工后将其用dbms_output输出。

同理，也可将结果Insert到表里去。

?

declare
x xmltype := xmltype('
 
 
  
    
	 
	  
       
         
	      
		   
			untrust-1
			untrust-2
		   
		   
			dmz1
			dmz2
		   
		   
			any
		   
		   
			DC01_FW01_eth11
		   
		   
			any
		   
		   
			any
		   
		   
			any
		   
		   
			service_https
			TCP_80
		   
		   
			any
		   
		   test
		   traffic_log
	    
       
      
	 
	
   
  
 

');

begin
for r in
(SELECT X.*
　FROM　XMLTABLE (
  -- 取得entry之下的from/to等Node的值，因为member可能有一个以上，要用for
  'for $e in $d/config/devices/entry/vsys/entry/rulebase/security/rules/entry
   return 
              --取得entry的Attribute，name
              {fn:string($e/@name)}
               --用函数(fn:string-join)把多个值连接起来，分隔符用;
               {fn:string-join($e/from/member,";")}
                 {fn:string-join($e/to/member,";")}
             {fn:string-join($e/source/member,";")}
        {fn:string-join($e/destination/member,";")}
        {fn:string-join($e/source-user/member,";")}
           {fn:string-join($e/category/member,";")}
        {fn:string-join($e/application/member,";")}
            {fn:string-join($e/service/member,";")}
       {fn:string-join($e/hip-profiles/member,";")}
                     {$e/action}  -- action只有一个值，不用函数
                     {$e/log-setting}
　 '
passing x as "d"
COLUMNS    s_entry     PATH 'entry',
                s_from PATH 'from',
                s_to   PATH 'to',
              s_source PATH 'source',
         s_destination PATH 'destination',
         s_source_user PATH 'source-user',
            s_category PATH 'category',
         s_application PATH 'application',
             s_service PATH 'service',
        s_hip_profiles PATH 'hip-profiles',
              s_action PATH 'action',
         s_log_setting PATH 'log-setting'
) AS X ) loop


dbms_output.put_line(
	       r.s_entry
	||','||r.s_from
	||','||r.s_to
	||','||r.s_source
	||','||r.s_destination
	||','||r.s_source_user
	||','||r.s_category
	||','||r.s_application
	||','||r.s_service
	||','||r.s_hip_profiles
	||','||r.s_action
	||','||r.s_log_setting

);

end loop;
end;

?

?

GlobalProtect,untrust-1;untrust-2,dmz1;dmz2,any,DC01_FW01_eth11,any,any,any,service_https;TCP_80,any,test,traffic_log

?

注意： 红字部分，“；”是有多个值的Node内部的分隔符，而“，”是各Node之间的分隔符。
?