How To Load XML values into a PL/SQL Collection.

The following example demonstrates how to load a PL/SQL table type collection from xml utilizing “bulk collect” and XMLSequence.

declare
   -- Declare collection type to bulk load into.
   type document_rt is record (
        doc_id         number,
        doc_name       varchar2(255)
        );
   type documents_tt is table of document_rt;

   docsXml        xmltype;
   docTable       documents_tt;
   docRecord      document_rt;
begin

   -- Create Example Xml for documents.
   docsXml := xmltype(
         '<Documents>'
      || '  <Document>'
      || '    <Id>111</Id>'
      || '    <Name>First Document</Name>'
      || '  </Document>'
      || '  <Document>'
      || '    <Id>222</Id>'
      || '    <Name>Second Document</Name>'
      || '  </Document>'
      || '</Documents>');

   -- Bulk load the docTable collection based on the xml.
   select extractValue( value( t ), 'Document/Id' ) doc_id,
          extractValue( value( t ), 'Document/Name' ) doc_name
     bulk collect into docTable
     from table( XMLSequence(
                    extract( docsXml, 'Documents/Document') ) ) t;

   -- Loop through collection to display results.
   if docTable.count > 0 then
      for i in docTable.FIRST..docTable.LAST loop
         docRecord := docTable( i );
         dbms_output.put_line( 'Id: ' || docRecord.doc_id );
         dbms_output.put_line( 'Name: ' || docRecord.doc_name );
      end loop;
   end if;
end;

Output

Id: 111
Name: First Document
Id: 222
Name: Second Document
Explore posts in the same categories: Oracle PL/SQL, Software Development

Tags:

You can comment below, or link to this permanent URL from your own site.

3 Comments on “How To Load XML values into a PL/SQL Collection.”

  1. KBS Says:

    This is what i was looking for…..thanks for the solution.

  2. naresh Says:

    what if there are million records the bulk collect going to fail ?

    • Tom Krueger Says:

      Hey Naresh,

      Anything with massive volume you need to consider the architecture design for handling that load. It is hard to advise without knowing details about your entire environment, but you may want to consider a staging table that you then process or a Message Queue to receive your xml message and then have it processed by many agents.

      Just to be clear, this solution likely won’t work to take millions of records into memory at once.

      Thanks,
      Tom


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: