title: flattening XML to load via SSIS package
body: <p>I have an XML output from Lotus Notes DB. Following is the XML structure:</p>
  
  <pre><code>&lt;Exec&gt;
     &lt;project id='C000253' type='Approved' &gt;
        &lt;DaysInOnHold&gt;39&lt;/DaysInOnHold&gt;
        &lt;DaysInCompleted&gt;0&lt;/DaysInCompleted&gt;
        &lt;ProjectComplexity type='1280'&gt;Complex&lt;/ProjectComplexity&gt;
        &lt;ChangeRequest&gt;
           &lt;OnGoingAmt type='768'&gt;-112&lt;/OnGoingAmt&gt;
           &lt;EAmt type='768'&gt;123&lt;/EAmt&gt;
        &lt;/ChangeRequest&gt;
        &lt;ChangeRequest&gt;
           &lt;ItemNbr type='768'&gt;2&lt;/ItemNbr&gt;
           &lt;EAmt type='768'&gt;321&lt;/EAmt&gt;
        &lt;/ChangeRequest&gt;
        &lt;IncidentalItem&gt;
           &lt;ACost type='768'&gt;0&lt;/ACost&gt;
           &lt;Billable type='1280'&gt;Billable&lt;/Billable&gt;
        &lt;/IncidentalItem&gt;
        &lt;IncidentalItem&gt;
           &lt;ACost type='768'&gt;33&lt;/ACost&gt;
           &lt;Billable type='1280'&gt;Billable&lt;/Billable&gt;
        &lt;/IncidentalItem&gt;
        &lt;MaterialItem&gt;
           &lt;AQty type='768'&gt;1&lt;/AQty&gt;
           &lt;ItemNbr type='768'&gt;4&lt;/ItemNbr&gt;
        &lt;/MaterialItem&gt;
        &lt;MaterialItem&gt;
           &lt;AQty type='768'&gt;0&lt;/AQty&gt;
           &lt;ItemNbr type='768'&gt;5&lt;/ItemNbr&gt;
        &lt;/MaterialItem&gt;
     &lt;/project&gt;
     &lt;project id='C110011' type='Not Approved'&gt;
       ...
        ...
     &lt;/project&gt;
  &lt;/Exec&gt;
  </code></pre>
  
  <p>When I load the XML source in my SSIS package, it creates a table for every column name, which of-course is not very desirable.</p>
  
  <p>I would like to transform this in such a way that it gives me the ability to have a Project table, MaterialItem and ChangeRequest tables (which would subsequently be related back to the individual project entry.</p>
  
  <p>Would XSLT be an answer? If so, any help would be appreciated.</p>
  
  <p>I thought I'd add in what the desired output should be (seems that all the type attributes are causing the issues). So seems that i need a transform that would get rid of all the attributes - specifically the type="...."</p>
  
  <pre><code>&lt;Exec&gt;
     &lt;project id='C0001111'&gt;
        &lt;DaysInOnHold&gt;33&lt;/DaysInOnHold&gt;
        &lt;DaysInCompleted&gt;0&lt;/DaysInCompleted&gt;
        &lt;ProjectComplexity&gt;Complex&lt;/ProjectComplexity&gt;
        &lt;ChangeRequest&gt;
           &lt;OnGoingAmt&gt;52&lt;/OnGoingAmt&gt;
           &lt;EAmt&gt;123&lt;/EAmt&gt;
        &lt;/ChangeRequest&gt;
        &lt;ChangeRequest&gt;
           &lt;ItemNbr&gt;2&lt;/ItemNbr&gt;
           &lt;EAmt&gt;321&lt;/EAmt&gt;
        &lt;/ChangeRequest&gt;
        &lt;IncidentalItem&gt;
           &lt;ACost&gt;0&lt;/ACost&gt;
           &lt;Billable&gt;Not Billable&lt;/Billable&gt;
        &lt;/IncidentalItem&gt;
        &lt;IncidentalItem&gt;
           &lt;ACost&gt;33&lt;/ACost&gt;
           &lt;Billable&gt;Billable&lt;/Billable&gt;
           &lt;/IncidentalItem&gt;
        &lt;MaterialItem&gt;
           &lt;AQty&gt;1&lt;/AQty&gt;
           &lt;ItemNbr&gt;4&lt;/ItemNbr&gt;
        &lt;/MaterialItem&gt;
        &lt;MaterialItem&gt;
           &lt;AQty&gt;0&lt;/AQty&gt;
           &lt;ItemNbr&gt;5&lt;/ItemNbr&gt;
        &lt;/MaterialItem&gt;
     &lt;/project&gt;
  &lt;/Exec&gt;
  </code></pre>