I have an XML source designed something like this: <xs:element name="item" maxOccurs="unbounded"> <xs:complexType> <xs:sequence> <xs:element name="Sub1" type="xs:string"/> <xs:element name="Sub2" type="xs:string"/> <xs:element name="RepeatingSub3" maxOccurs="unbounded" type="xs:string"/> <xs:element name="Sub4" type="xs:string"/> </xs:sequence> </xs:complexType> </xs:element> When I load this into an XML Source Component, it creates two outputs: one for Item and one for RepeatingSub3. I have a table as my destination which has four columns: Sub1, Sub2, RepeatingSub3, and Sub4. Ultimately, I want each Item to map to one row in my table. The only way I can think to get this done is to use a script component to transpose RepeatingSub3 to comma separated values, then merge the results with the rest of the subelements to create a single row output for each Item. The problem is that my real schema has 5 of these repeating subelements, which means I've got to transform 5 outputs, then do 5 or 6 separate Merge Joins to get all the pieces back together (since I can't find a way to join more than two sources at a time). Is there an easier way to get this done? Thanks in advance!

What you show is the schema, not the actual XML.Arthur My Blog

Thanks for the reply, ArthurZ. I'm aware that I posted the schema, and that was intentional. The XML follows the schema and would look something like this: <Item name="ItemName"> <Sub1>Sub1Data</Sub1> <Sub2>Sub2Data</Sub2> <RepeatingSub3>Sub3Data_1</RepeatingSub3> <RepeatingSub3>Sub3Data_2</RepeatingSub3> <RepeatingSub3>Sub3Data_3</RepeatingSub3> <Sub4>Sub4Data</Sub4> </Item> I'm trying to keep it simplified because my question is on SSIS, not XML design. I have no control over the design of the XML source.

Thanks for the reply, ArthurZ. I'm aware that I posted the schema, and that was intentional. The XML follows the schema and would look something like this: <Root> <Item name="ItemName"> <Sub1>Sub1Data</Sub1> <Sub2>Sub2Data</Sub2> <RepeatingSub3>Sub3Data_1</RepeatingSub3> <RepeatingSub3>Sub3Data_2</RepeatingSub3> <RepeatingSub3>Sub3Data_3</RepeatingSub3> <Sub4>Sub4Data</Sub4> </Item> </Root> I'm trying to keep it simplified because my question is on SSIS, not XML design. I have no control over the design of the XML source. [Edit: The item is wrapped in a Root Node, I just neglected to show that when I included this example]

The absence of the external wrapper in your XML produced that undesired result, since you do not have the control over the XML file (you can surround it with an external element still using a script component) I can offer you to solve this by using a Merge Join Component. You can simply see the solution toward the bottom here: http://blogs.msdn.com/b/mattm/archive/2007/12/11/using-xml-source.aspx Arthur My Blog

The problem is that the Merge Join Component only allows for two inputs, which would require that I use multiple Merge Joins to get all my data back together. My initial question was, is there an easier way to do this rather than splitting the data, converting it, then merging it all together with multiple joins?

The easiest is to wrap the XML in a root node. Then all the columns will show up in one source. Once you get the file use a Script Task to wrap around this extra element. In the Script Task manipulate on the file using FileSystem.Arthur My Blog

The easiest is to wrap the XML in a root node. Then all the columns will show up in one source. Once you get the file use a Script Task to wrap around this extra element. In the Script Task manipulate on the file using FileSystem or XMLWriter like in this post: http://www.codeproject.com/KB/XML/dcinsertxml.aspx Arthur My Blog

I corrected my previous example to show that a root node does exist, I just neglected to include it when I typed the example above. Another example to help illustrate the problem: <Root> <Resident> <ResidentID>11111</ResidentID> <ResidentName>John</ResidentName> <Child>Jimmy</Child> <Child>Mary</Child> <Child>Johnny</Child> <Car>Pinto</Car> <Car>Gremlin</Car> <Car>Vespa</Car> <Spouse>Jill</Spouse> </Resident> </Root> Obviously this isn't the actual data, but hopefully it represents what I'm trying to accomplish. Essentially, I want my output for this Resident to return a single row: ResidentID | ResidentName | Children | Cars | Spouse 11111 | John | Jimmy,Mary,Johnny | Pinto, Gremlin, Vespa | Jill

Then you may want to explore an XML Task to run a transform operation using XSLT against your XML. Example: http://blogs.msdn.com/b/mattm/archive/2007/12/15/xml-source-making-things-easier-with-xslt.aspx Arthur My Blog