I'm trying to import a single xml file into a single SQL 2008 R2 table using SSIS. The xml file has many elements so SSIS wants to put them into separate tables. I found how to use the merge to put them into a single table but it splits the data from the two elements into separate rows. Please see below for an example.

WHAT I HAVE.
simplified partial xml file
<element1>name</element1>
<attribute1>john</attribute1>
<element2>address</element2>
<attribute1>123 any place</attribute>

Inserted data
ROW1 john ,null
ROW2 null ,123 any place

WHAT I WANT
ROW1 john ,123 any place

Maybe one of you out there has done this before. The join does NOT have andcommon data EXCEPT that it is all from the same file so they are related by default!

My DataFlow looks like

xml file datasource1 xml file datasource2 (both are pointing to the same file)


Thanks in advance
~Jay