小毛驢（xml）試乘記（二）：pl/sql方法，xmlpl

小毛驢（xml）試乘記（二）：pl/sql方法，xmlpl

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"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 ) 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之間的分隔字元。