Oracle支持xml。可以用pl/sql加工xml数据。
下面的例子对xml加工后将其用dbms_output输出。
同理,也可将结果Insert到表里去。
declare
x 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>
');
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 <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"
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之间的分隔符。