Using a PL/SQL List to Process an XML Document > 32k
The XMLType has a member function that allows you to instantiate an XML object from a varchar2. Example:
declare v_xmltext varchar2(32767) := '<row> <os_name>Red Hat Enterprise Linux Server release 5.4 (Tikanga)</os_name> <vendor_name>Red Hat</vendor_name> <base_version>2.6.18</base_version> <update_level>164.el5</update_level> <platform_id>227</platform_id> <platform_version_id>20005</platform_version_id> </row>'; v_xml xmltype; v_name varchar2(256); begin v_xml := xmltype(v_xmltext); v_name := v_xml.extract('/row/os_name/text()').getStringVal(); dbms_output.put_line(v_name); end; /
This works fine for documents that are less than 32 kbytes in length. This is the maximum lenth for a varchar2 variable in pl/sql. XML documents that are longer than this typically have more than one record contained within them. They can be processed by creating a table of XMLTypes and processing each record as a separate entry in the table. Example to process a large XML document in the form:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <Row> <OS_Name>Red Hat Enterprise Linux AS release 4 (Nahant Update 8)</OS_Name> <Vendor_Name>Red Hat</Vendor_Name> <Base_Version>2.6.9</Base_Version> <Upate_Level>89.EL</Upate_Level> <Platform_ID>214</Platform_ID> <Platform_Version_ID>6009</Platform_Version_ID> </Row> <Row> <OS_Name>Red Hat Enterprise Linux AS release 4 (Nahant Update 7)</OS_Name> <Vendor_Name>Red Hat</Vendor_Name> <Base_Version>2.6.9</Base_Version> <Upate_Level>78.0.1.EL</Upate_Level> <Platform_ID>214</Platform_ID> <Platform_Version_ID>6009</Platform_Version_ID> </Row> <Row> <OS_Name>Red Hat Enterprise Linux Server release 4 (Tikanga)</OS_Name> <Vendor_Name>Red Hat</Vendor_Name> <Base_Version>2.6.18</Base_Version> <Upate_Level>92.el5</Upate_Level> <Platform_ID>214</Platform_ID> <Platform_Version_ID>6009</Platform_Version_ID> </Row> </Root>
Use a text editor to convert the XML document into a series of PL/SQL table entries:
declare type xmllist_t is table of xmltype; os_list xmllist_t := xmllist_t( xmltype('<row> <OS_Name>Red Hat Enterprise Linux AS release 4 (Nahant Update 8)</OS_Name> <Vendor_Name>Red Hat</Vendor_Name> <Base_Version>2.6.9</Base_Version> <Upate_Level>89.EL</Upate_Level> <Platform_ID>214</Platform_ID> <Platform_Version_ID>6009</Platform_Version_ID> </row>'), xmltype('<row> <OS_Name>Red Hat Enterprise Linux AS release 4 (Nahant Update 7)</OS_Name> <Vendor_Name>Red Hat</Vendor_Name> <Base_Version>2.6.9</Base_Version> <Upate_Level>78.0.1.EL</Upate_Level> <Platform_ID>214</Platform_ID> <Platform_Version_ID>6009</Platform_Version_ID> </row>'), xmltype('<row> <OS_Name>Red Hat Enterprise Linux Server release 4 (Tikanga)</OS_Name> <Vendor_Name>Red Hat</Vendor_Name> <Base_Version>2.6.18</Base_Version> <Upate_Level>92.el5</Upate_Level> <Platform_ID>214</Platform_ID> <Platform_Version_ID>6009</Platform_Version_ID> </row>')); v_name varchar2(256); begin for i in os_list.first .. os_list.last loop v_name := os_list(i).extract('/row/OS_Name/text()').getStringVal(); dbms_output.put_line(v_name); end loop; end; / Red Hat Enterprise Linux AS release 4 (Nahant Update 8) Red Hat Enterprise Linux AS release 4 (Nahant Update 7) Red Hat Enterprise Linux Server release 4 (Tikanga) PL/SQL procedure successfully completed. SQL>