I am trying to use the SSIS XML Source object to insert data from an XML file into a relational database. Within the file and database there are several one to many relationships. For example, the Person table has a one to many relationship with
the Phone table - one person can have multiple phone numbers.
In my XML file, the PersonID is declared only once and that is with the person data. I need the PersonID as a foreign key for the phone data. The XML Source object displays multiple outputs for the XML file, including one each for Person and
Phone. However the Phone output does not include the PersonID which is required to insert the data into the Phone table.
How can I get the PersonID to appear with the Phone data? Is it a matter of modifying the XSD file, or is there method within SSIS where I can combine XML elements from different levels?
View Complete Post