Hello,
I am very new to SSIS and working on importing Nested XML file into SQL Tables (Parent Child).
What should be the best way to achieve this SSIS 2005?
Please see the sample XML and Schema below.
The family details should go into a Main Table, and the children details should go to a child table that as the Family GUID as a column.
Here is what I did:
- Have XML Source pointing to XML Date and Schema file.
- I have 3 outputs now: family, childrens and children.
- Sorted the childrens and children output and gave input to Merge Join so that I can have the children along with the family_id
- Able to get the family details in to Family Table.
- Getting childrens for each family is not working.
Please suggest a best way to achieve this using SSIS.
Thanks.
XML:
<FamilyGuid>94A9565D-766C-4043-8ACD-C7E2B630B6E3</FamilyGuid>
<FamilyName>Family Name 1</FamilyName>
<Father>Father 1</Father>
<Mother>Mother 1</Mother>
</Family>
<FamilyGuid>522CDCA4-A52D-468F-9875-BE856C06D4F0</FamilyGuid>
<FamilyName>F Name 2</FamilyName>
<Father>Father 2</Father>
<Mother>Mother 2</Mother>
<Children>Ch 11</Children>
</Childrens>
</Family>
</Families>
XSD:
<?
xml version="1.0" encoding="utf-8"?>xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" >xs:element name="Families">xs:complexType>xs:sequence>xs:element name="Family" minOccurs="0" maxOccurs="unbounded" >xs:complexType>xs:sequence>xs:element name="FamilyGuid" type="Guid" />xs:element name="FamilyName" type="xs:string" />xs:element name="Father" type="xs:string" />xs:element name="Mother" type="xs:string" />xs:element name="Income" type="xs:unsignedShort" />xs:element name="Childrens" minOccurs="0" >xs:complexType>xs:sequence>xs:element name="Children" type="xs:string" minOccurs="0" maxOccurs="unbounded" />xs:sequence>xs:complexType>xs:element>xs:sequence>xs:complexType>xs:element>xs:sequence>xs:complexType>xs:element>xs:simpleType name="Guid">xs:restriction base="xs:string">xs:minLength value="36" />xs:maxLength value="36" />xs:restriction>xs:simpleType>xs:schema>
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
</
</
</
</
</
</
</
</
</
<
<
<
<
</
</
</