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>