Export XML file using SSIS

In this blog we will see how to export XML file using SSIS. I chose to demonstrate XML but it works the same for all BLOB strings (text, ntext).

Let us export this simple xml query to XML file

select cast('<!--?xml version="1.0" encoding="UTF-16" ?-->'+
(
	SELECT TOP(20) CAST(
	'<' + FirstName +'_'+ LastName + '>'+
		(SELECT child.FirstName, 
				child.LastName 
		 FROM   person.person child		 
		 FOR XML PATH('')) + 
	'' 
	AS xml) as SSAXML
	FROM   person.person parent
	for xml PATH(''), ROOT('person')
) as XML) as  xmloutput	;

if we execute the query on SSMS for AdventureWorks database we will get this an XML that looks like this one.

<person>
   <Syed_Abbas>
      <FirstName>Syed</FirstName>
      <LastName>Abbas</LastName>
      <FirstName>Catherine</FirstName>
      <LastName>Abel</LastName>
      <FirstName>Kim</FirstName>
      <LastName>Abercrombie</LastName>
    </Syed_Abbas>
</person>

Let us export this xml query to person.xml file. Let us begin by adding a data flow task

Now add an OLEDb source and add the XML Script to it

Now drop a Script component and select Destination type

Now open the Script Component and check the XML input column

Then select the script tab and click on Edit Script…
Include System.IO library to your script.

Now update your Input0_ProcessInputRow function as

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        byte[] blob;
        string fileContent;
        blob = Row.xmloutput.GetBlobData(0, 
                   int.Parse(Row.xmloutput.Length.ToString()));
        fileContent = System.Text.Encoding.ASCII.GetString(blob);
        TextWriter tw = new StreamWriter(@"D:\person.xml");
        tw.WriteLine(fileContent);
        tw.Close();
    }

Where xmloutput is going to be replaced by your XML column name. Finally execute your package

Now let us check if our XML file is created properly

Yap it is created and filled with our data properly.

Update 3/30/2014:
Kevin asked me he found additional character at the end of the XML file he generated and a way to remove that character.

There is a carriage return at the end of the XML file. To remove that we could add another script task after the data flow task that generates the XML.

The open of the Script task and add the following C# script.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
 
public void Main()
{
            string strPath = @"D:\person.xml";
            string fileContent = System.IO.File.ReadAllText(strPath);
            try
            {
                System.IO.File.WriteAllText(strPath,
                    Regex.Replace(fileContent, "\r\n", "")
                    .Replace("\'", "&quot;")
                    .Trim());
 
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex) { throw ex; }
 
	Dts.TaskResult = (int)ScriptResults.Success;
}
 
/*Replace the D:\person.xml with your actual path and file name of your xml*/

It should look like this on your SSDT or BIDS

This should fix the problem with the carriage return and check your XML file and to make sure.