(Working in SSIS for first time without any knowledge) Hi, I have a small task to read XML file and transfer data into SQLServer Table. I bing to find a tutorial which address following challanges, but could not find it. Can someone help to find it. 1. XML File will contain parent-child data so data needs to be stored in multiple tables. 2. There are fields in XML which contains string values like "Hot", "Cold" etc. these needs to be converted into numeric values while storing into sqltables. and numeric values for "Hot", "Cold" will come from a 'Status Table' existing in same SQL database. 3. The other challange i can see, is how do i insert parent-child rows when primarykey is AutoNumber in target sql server table. Regards Jiya

Without knowing how the parent-child relationship is defined in your XML it is probably best for me to tell you to use the XML Source and an OLEDb destination to a staging table from which you can propagate the data to the needed other tables, #2 can be done at the that stage, too. A good (and an advanced example could be this: http://blogs.msdn.com/b/mattm/archive/2007/12/11/using-xml-source.aspx). Regarding #3 - you probably expect difficulties re-establishing the relationship once the records are in the database. If so, this is not a SSIS issue, but a database design one. Again, without knowing how the xml looks like and how the database relates the records it is not quite possible to answer reliably this portion.Arthur My Blog

Without knowing how the parent-child relationship is defined in your XML it is probably best for me to tell you to use the XML Source and an OLEDb destination to a staging table from which you can propagate the data to the needed other tables, #2 can be done at the that stage, too. A good (and an advanced example could be this: http://blogs.msdn.com/b/mattm/archive/2007/12/11/using-xml-source.aspx). Regarding #3 - you probably expect difficulties re-establishing the relationship once the records are in the database. If so, this is not a SSIS issue, but a database design one. Again, without knowing how the xml looks like and how the database relates the records it is not quite possible to answer reliably this portion.Arthur My Blog

Hi ArthurZ, Keeping a side on my original question. I have some other question here. I was trying to use XML Source, Selected an XML file containing below XML and choose to 'Generate XSD' file using option in XMLSource Component. Case1: <goo> <subgoo>value</subgoo> <moregoo>1</moregoo> </goo> The XML component does not list columns, then i tried to add root element as below and it worked. Case2: <rootgoo> <goo> <subgoo>value</subgoo> <moregoo>1</moregoo> </goo> </rootgoo> So my question is , Is root element necessary, because there is a system which generates XML as in 1st case above. - Jiya.

Yes, it has to be, otherwise the XML is not well-formed.Arthur My Blog