Loading XML into table with SSIS
Posted on 2012-03-28
I have been given an XML file and XSD that I need to load into a relational table structure. I am fairly new to working with both XML and SSIS, but I have setup an XML source and an ADO.NET destination. When I use the XML Source Editor and look at the "Columns", I see numerous output names. I was not expecting this since the XML records themselves are mostly non-hierarchical. However, I noticed that there is something different in some of my XML elements and those elements are showing up as different outputs.
Many of my XML elements look like normal:
But some have an additional parameter:
Any element that has this code="0" or similar code= reference is being setup as a separate output. I really only want the "No" value for the ACTIVEFLAG element. I don't need the code value (which I assume is an alternative value for the element). Is there a way to tell SSIS to ignore the code= and hopefully reduce the number of outputs that are being created?
This is the smallest of my XML files that I need to convert and there are over 90 outputs generated by the XML Source Editor. My intent was to use a MERGE JOIN to bring elements together if needed, but that would be unmanageable with 90 outputs.