XML source and multiple data outputs for elements.

If you have ever had to deal with a typical XML file for data loading for ETL tasks in SSIS then  you have encountered its default behavior of creating an output for every element.  This is not the nicest behavior because it is doubtful that anyone would have a database so normalized.  While working on such an issue we discovered a useful trick.  First off, SSIS has not left you in no mans land.  If you have generated outputs for each element you can write all the elements to a Data Destination and use a SQL join statement to reconstitute the data in whatever way you want.  SSIS automatically adds IDs to all the tables it creates so that you can do join statements based on the hierarchy of the XML.  For example, if your xml hierarchy looks like this:

Parent Element->

                                Child Element->

                                                              Inner Child Element

 

Then the tables that SSIS would create will have an ID element for InnerChildElement that makes it joinable to Child Element and an ID field inside Child Element that makes it joinable to Parent Element.  This can be a life saver as you will just have to do one join to get the data back in a form that is interpretable as hierarchy.  This added ID field allows a second possibility:  Using Merge Joins on the output to create denormalized tables.  The SSIS Team actually have a blog entry that goes over this (http://blogs.msdn.com/mattm/archive/2007/12/11/using-xml-source.aspx).

Long story short, XML source does exactly what you will want it to for importing XML data.  That and the XSD validation capability of XML Task (note watch your constraints after the XML task to ensure that if you are validating xml you will get the proper success result) makes SSIS a complete XML handling solution.