title: Process multi-level xml with collection in element
body: <p>I have an xml document that I would to parse using SSIS 2005 to an SQL table.
  But I'm having some trouble with it because it is multi-level and contains collections(?) in each post.
  I have found a solution to get multilevel XML to one row using Merge Join in SSIS, but I can't figure out a way to handle the multiple &lt;adress&gt; elements to get them to one row.</p>
  
  <p>Hope someone can help me out with this.</p>
  
  <p>Edit:<br/>
  So I would like the output to contain the following data in one row.<br/></p>
  
  <pre><code>Personnummer, fornamn, efternamn, kon, epost, avdelning, foretagsnr, anstnr, 
  arbetsledare, signatur, pkontering3, adress.hemadress.gatuadress, 
  adress.hemadress.adress2, adress.hemadress.co_adress, adress.hemadress.postnr,
  adress.hemadress.postort, adress.hemadress.land, adress.mobiltelefon.telefonnr,
  adress.hemtelefon.telefonnr
  </code></pre>
  
  <p>Hope you understand my adress.hemadress/mobiltelefon/hemtelefon notation. The &lt;befattningar&gt; element is not used atm, and if it will be used the same solution for &lt;adresser&gt; will probably work. :)</p>
  
  <p>Here's an example of the xml structure, as you can see there are three &lt;adress&gt; elements in  the &lt;adresser&gt; element, and I would like them all to be output to a single row. If it was possible to ignore some of the elements inside the &lt;adress&gt; element based on the text in the &lt;adresstyp&gt; element that would be great, but I guess I can manage without that functionality.</p>
  
  <pre><code>&lt;PersonCollection&gt; 
    &lt;Person&gt;
      &lt;Personnummer&gt;190001010101&lt;/Personnummer&gt; 
      &lt;Fornamn&gt;firstname&lt;/Fornamn&gt; 
      &lt;Efternamn&gt;lastname&lt;/Efternamn&gt; 
      &lt;Kon&gt;K&lt;/Kon&gt;
      &lt;Epost&gt;mail@mail.com&lt;/Epost&gt; 
      &lt;Avdelning&gt;B&lt;/Avdelning&gt; 
      &lt;Foretagsnr&gt;1&lt;/Foretagsnr&gt; 
      &lt;Anstnr&gt;1&lt;/Anstnr&gt; 
      &lt;Arbetsledare&gt;firstname lastname&lt;/Arbetsledare&gt;
      &lt;Signatur&gt;X&lt;/Signatur&gt; 
      &lt;PKontering3&gt;XXXX&lt;/PKontering3&gt; 
      &lt;Befattningar&gt; 
          &lt;Befattning&gt; 
              &lt;Status&gt;X&lt;/Status&gt;
              &lt;Namn&gt;Position&lt;/Namn&gt; 
          &lt;/Befattning&gt; 
      &lt;/Befattningar&gt; 
      &lt;Adresser&gt;
          &lt;Adress&gt;
              &lt;Adresstyp&gt;Hemadress&lt;/Adresstyp&gt; 
              &lt;Telefonnr /&gt; 
              &lt;Gatuadress&gt;Streetadress 1&lt;/Gatuadress&gt; 
              &lt;Adress2 /&gt; 
              &lt;Co_Adress /&gt; 
              &lt;Postnr&gt;111 22&lt;/Postnr&gt; 
              &lt;Postort&gt;City&lt;/Postort&gt; 
              &lt;Land&gt;Country&lt;/Land&gt; 
          &lt;/Adress&gt;
          &lt;Adress&gt;
              &lt;Adresstyp&gt;Mobiltelefon&lt;/Adresstyp&gt; 
              &lt;Telefonnr&gt;010-010 01 01&lt;/Telefonnr&gt;
              &lt;Gatuadress /&gt; 
              &lt;Adress2 /&gt; 
              &lt;Co_Adress /&gt; 
              &lt;Postnr /&gt; 
              &lt;Postort /&gt; 
              &lt;Land /&gt; 
          &lt;/Adress&gt;
          &lt;Adress&gt;
              &lt;Adresstyp&gt;Hemtelefon&lt;/Adresstyp&gt; 
              &lt;Telefonnr&gt;01-01 01 01&lt;/Telefonnr&gt; 
              &lt;Gatuadress /&gt; 
              &lt;Adress2 /&gt; 
              &lt;Co_Adress /&gt; 
              &lt;Postnr /&gt; 
              &lt;Postort /&gt; 
              &lt;Land /&gt; 
          &lt;/Adress&gt;
      &lt;/Adresser&gt;
    &lt;/Person&gt;
  &lt;/PersonCollection&gt;
  </code></pre>