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.
