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




Related Articles :

6 Responses to “Export to XML Using SSIS”

  1. Thanks for highlighting the fact that SSIS has a hole when it comes to outputting an XML file that makes us turn to a Script Task. I am curious why you bothered to use an XML Document object. If you didn’t want the root tags, then why not just write the data coming from the database to the file using File.Write? Also, when it comes to building a path with two strings Path.Combine is safer than concatenating things yourself.

  2. FYI – if you’re able to modify the SQL in any way, you can CAST(@XML as varchar(max)) and export that as a string without needing a Script task. I did that for some time with some data feeds we were doing and had no problems with the XML files generated.

  3. Actually there is an XML Task that will work for this.
    Change the result set to single row, that eliminates the . In the XML task set the operation type to XPATH, Source Type to Variable and Select your XML variable. Set SaveOperationResult to True, expand OperationResult, and set it to a file connection with OverwriteDestination = True. Set the SecondOperandType to Direct input and type /AllObjects as the SecondOperand. Set PutResultsInOneNode to False and the XpathOperation to Node list.

  4. Or you can keep the Result Set as XML and use /ROOT/AllObjects as the Second Operand

  5. Thanks! Worked perfectly.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |