How to do database insert in mule with bulk mode on for XML input

I have input XML as payload and i want to insert the XML values into database columns using bulk mode on. Mule documentation shows bulk insert can be done only with collections. If it can be done by implementing collections, How can we convert xml to collection then do bulk insert instead of For-each loop which is time consuming.

Mule version used - v3.8

Lets take sample below,

Sample XML as payload input,

<notes>
  <note number ="1">
    <to>Tove</to>
    <from>Jani</from>
    <heading>Reminder</heading>
    <body>Don't forget me this weekend!</body>
    <address>
        <mode = "Email">
        <fulladdress>[email protected]</fulladdress>
    </address>
    <organisation>
        <designation type="contractor">Software engineer</designation>
    </organisation>
  </note>
  <note>
  <note number ="2">
    <to>Smith</to>
    <from>Gerri</from>
    <heading>Hello</heading>
    <body>Hi Smith, nice to meet you.</body>
    <address>
        <mode = "Email">
        <fulladdress>[email protected]</fulladdress>
    </address>
    <organisation>
        <designation type="permanent">Software engineer</designation>
    </organisation>
  </note>
</notes>

Please answer the below two questions, 1. How to convert this input XML into collection? 2. How to achieve bulk insert with the collection created from question 1?

Solution

Let's assume you're using Mule 3 and your payload looks like this:

<notes>
  <note>
    <to>Tove</to>
    <from>Jani</from>
    <heading>Reminder</heading>
    <body>Don't forget me this weekend!</body>
  </note>
  <note>
    <to>Smith</to>
    <from>Gerri</from>
    <heading>Hello</heading>
    <body>Hi Smith, nice to meet you.</body>
  </note>
</notes>

You could use DataWeave to transform this into a collection of Java objects representing a note like this:

%dw 1.0
%output application/java
---
payload.notes.*note

Then you can do your bunk insert after the transform.