Hello Baskaran, I tried to be too brief in my previous post. I'm going to elaborate a little more here. Can you post an example piece of XML data that you are using?
1) The first step is to get the data into a SQL table. At first, to get going, just keep it 1 flat denormalized SQL table of data.
2) Using Management Studio, build you XML query using OPTION EXPLICIT and get the XML well formatted as you would eventually like to see it in an XML file. The above MSDN web address will explain in full detail how to use OPTION EXPLICIT to get what you
want. Once you have the quary ready to go. Place this into an "Execute SQL Task". In "General", set the "ResultSet" property to "XML". In "Result Set" tab, set "Result Name" = 0 and the variable name
to a string variable, which you can create at this point, for example I've created one called "PersonXML".
3) Then using a script task and using the variable as a ReadOnly variable. Here is the example of how we wrote the XML data out to a flat file. The connection was previously created as "agm_person.xml".
String XMLString = null;
String XMLFileName = Dts.Connections["agm_person.xml"].AcquireConnection(Dts.Transaction).ToString();
StreamWriter fs = new StreamWriter(XMLFileName, false);
XMLString = Dts.Variables["PersonXML"].Value.ToString().Replace("<ROOT>", "").Replace("</ROOT>", "");
Hope this helps a little more. I a little busy today to give the complete details.