I tried the solution in my previous question (flattening XML to load via SSIS package), however this isn't working. I now know what I need to do, however I need some guidance on how to do it.
So say I have the following XML structure:
<person id="1">
<name>John</name>
<surname>Smith</surname>
<age>25</age>
<comment>
<comment_id>1</comment_id>
<comment_text>Hello</comment_text>
</comment>
<comment>
<comment_id>2</comment_id>
<comment_text>Hello again!</comment_text>
</comment>
<somethingelse>
<id>1</id>
</somethingelse>
<comment>
<comment_id>3</comment_id>
<comment_text>Third Item</comment_text>
</comment>
</person>
<person id="2">
<name>John</name>
<surname>Smith</surname>
<age>25</age>
<somethingelse>
<id>1</id>
</somethingelse>
</person>
...
...
If I am to load this into a SSIS package, as an XML source, what I will essentially get is a table created for each element, as opposed to get a structured table output such as
person table (name, surname, age)
somethingelse table (id)
comment table (comment_id, comment_text)
What I end up getting is:
person table (person_Id <-- internal SSIS id)
name table
surname table
age table
person_name table
person_surname table
person_comment_comment_id table
etc...
What I found was that if each element and all inner elements are not in the same format and consistency, i will get the above anomaly which makes it rather complex especially if I am dealing with 80 - 100+ columns.
Unfortunately I have no way of modifying the system (Lotus Notes) that produces these reports, so I was wondering whether I may be able to explicitly have an XSLT template that will be able to align each person sub elements (and the sub collection elements such as comments ? Unless there is a quicker way to realign all inner elements.
Seems that SSIS XML source requires a very consistent XML file in the sense of: if the name element is in position 1, then all subsequent name elements within person parent have to be in position 1.
SSIS seems to pickup the inconsistencies if there are certain elements missing from one parent to another, however, if their ordering is not right (A, B, C)(A, B, C)(A,C,B), it will chuck a massive fuss!
All help is appreciated! Thank you in advance.
