SSIS - Sql Server to XML - Save to file

As you know there is no XML destination in SSIS.
so how you can save result of a query from sql server to XML file?
Solution is:

First Of all you can use FOR XML to get result of query in XML , look at our sample query :

SELECT EmployeeID,NationalIDNumber,ContactID
FROM HumanResources.Employee
FOR XML RAW('Facility'),Root('Extract'),Elements  

 

this will creates elements with name 'Facility', and attributes 'EmployeeID' , 'NationalIDNumber' , 'ContactID'

and the Root node is 'Extract'.

for more information about FOR XML in sql server look here .

So, start with SSIS:

1- Create a Variable of String type in package scope, and name it as XMLData. 

2- Add an Execute SQL Task, set connection as OLEDB to the AdventureWorks Database ( If you haven't AdventureWorks sample database, download it from here ),

write this query in SqlStatement:

SELECT EmployeeID,NationalIDNumber,ContactID
FROM HumanResources.Employee
FOR XML RAW('Facility'),Root('Extract'),Elements  

Set ResultSet property to XML.

then go to Result Set tab, and do this mapping:

Result Name                         Variable Name

--------------------------------------------

 0                                      User::XMLData

3- Add a Script Task after execute sql task, set language as C#. and set ReadOnlyVariables as User::XMLData .

then edit script and write this code in Main() method:

public void Main()
        {
            System.Xml.XmlDocument xdoc = new System.Xml.XmlDocument();
            xdoc.InnerXml = Dts.Variables["XMLData"].Value.ToString();
            xdoc.Save(@"E:\Output.xml");

            Dts.TaskResult = (int)ScriptResults.Success;
        }

 whole schema:

Finished !

now just run the package and result of query will save in xml file specified.