Export to XML Using SSIS

Recently I was given a task to export data in XML format using SSIS. I initially thought it should be straight forward – just dump it in a flat file and give it a name xml type, however SSIS has no XML destination just an XML source. I had no luck dumping the XML in flat file. My solution was to use a script task which worked well.

My requirement is that the stored procedure would return the XML as a single row and I need to export that in a file.

I will run through this solution by example. Below is my stored procedure, which will return an XML row

        ALTER PROCEDURE ExporttoExcel
AS
BEGIN
DECLARE @XML XML
SELECT @XML = (SELECT DataXML FROM
                        (SELECT name as "name" ,
                        object_id as "objectid"
                        FROM sys.objects
                        FOR XML PATH ('Objects'), ROOT('AllObjects')
                        )D(DataXML))
SELECT  DataXML =@XML
END
    

The output of the above stored procedure will be as below :

<AllObjects>
<Objects>
<name>sysrscols</name>
<objectid>3</objectid>
</Objects>
<Objects>
<name>sysrowsets</name>
<objectid>5</objectid>
</Objects>
…
</AllObjects> 

Now the stored procedure is created, let us start with the SSIS package. The first task is to drag and drop the Execute SQL Task. This Task will execute the stored procedure and return  the result set of XML in a string variable.

Next configure the properties inside Execute SQL task

Note that, the Result Set will be XML, and ConnectionType will be ADO.NET. In order to return an XML result only the ADO.NET Connection Manager has to be used. Be careful while setting the parameters to your stored procedure in ADO.NET connection, as the parameter passing method is different in ADO.NET and OLEDB. In ADO.NET you need to pass the parameter as @para instead of ?

Here, we dont have any parameters, so we will ignore this. We declare three package level variables

DataXML – Will contain the XML returned by the stored procedure

FileName – If there is specific file name to be given, set it as a variable.

FilePath – If the file location is also variable, this variable will be set.

On the Result Set select the variable defined.

Here, when we store the XML value in a string variable the XML display format changes to one single line and it no longer displays in a proper XML format.

So, the next step is to add a Script task

Set the three variable defined earlier as Read only variables inside the script task :

Edit the Script and go to the void main function. The first step in the script task, is to fetch the data from the DataXML string variable into a new variable inside the task. Since the variable is a string and we want to export to a XML file, we need to convert the string variable to an XMLDocument. Declare an xdoc XMLDocument variable, and using the LoadXML function, convert the string to XML. Put the outputfile path into another variable. Lastly, write the xdoc variable to the outputfile path. Save the script and exit.

        public void Main()
        {
            // TODO: Add your code here
            string Data = Dts.Variables["DataXML"].Value.ToString();
            System.Xml.XmlDocument xdoc =  new XmlDocument();
            xdoc.LoadXml(Data.ToString());
            string outputFile = Dts.Variables["FilePath"].Value.ToString() + "\\" + Dts.Variables["FileName"].Value.ToString();
            xdoc.Save(outputFile);
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    

Now, run the package :

The file is now exported to the expected path.

Next, open the file :

The file has a new Root tag which means when the Execute SQL tasks writes the DataXML variable with XML result set, it always adds a Root Tag. Since we don’t need the extra Root tag, we can remove it in the script task. Replace the earlier code to get xml in Data variable with the new code as below.

        public void Main()
        {
            // TODO: Add your code here
           // string Data = Dts.Variables["DataXML"].Value.ToString();
Dts.Variables["DataXML"].Value.ToString().Replace("<ROOT>", "").Replace("</ROOT>", "");
            System.Xml.XmlDocument xdoc =  new XmlDocument();
            xdoc.LoadXml(Data.ToString());
            string outputFile = Dts.Variables["FilePath"].Value.ToString() + "\\" + Dts.Variables["FileName"].Value.ToString();
            xdoc.Save(outputFile);
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    

Now after running the package, the file is as expected

]]>