Oracle支持xml。可以用pl/sql加工xml数据。
下面的例子对xml加工后将其用dbms_output输出。
同理，也可将结果Insert到表里去。
declarex xmltype := xmltype('<?xml version="1.0"?><config> <!–在config和rules之间有许多数据，这里简化–> <devices> <entry><vsys> <entry> <rulebase><security><rules> <!–rules之下的entry有上百条，这里简化，只留1条数据–><entry name="GlobalProtect"><from><member>untrust-1</member><member>untrust-2</member></from><to><member>dmz1</member><member>dmz2</member></to><source><member>any</member></source><destination><member>DC01_FW01_eth11</member></destination><source-user><member>any</member></source-user><category><member>any</member></category><application><member>any</member></application><service><member>service_https</member><member>TCP_80</member></service><hip-profiles><member>any</member></hip-profiles><action>test</action><log-setting>traffic_log</log-setting></entry></rules></security> </rulebase></entry> </vsys> </entry> </devices></config>');beginfor 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 <e>–取得entry的Attribute，name<entry>{fn:string($e/@name)}</entry>–用函数(fn:string-join)把多个值连接起来，分隔符用;<from>{fn:string-join($e/from/member,";")}</from><to>{fn:string-join($e/to/member,";")}</to><source>{fn:string-join($e/source/member,";")}</source><destination>{fn:string-join($e/destination/member,";")}</destination><source-user>{fn:string-join($e/source-user/member,";")}</source-user><category>{fn:string-join($e/category/member,";")}</category><application>{fn:string-join($e/application/member,";")}</application><service>{fn:string-join($e/service/member,";")}</service><hip-profiles>{fn:string-join($e/hip-profiles/member,";")}</hip-profiles>{$e/action} — action只有一个值，不用函数{$e/log-setting} </e>'passing x as "d"COLUMNSs_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 ) loopdbms_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之间的分隔符。
往往为了自己的不能失败，而处心积虑前怕狼后怕虎，
