Friday, June 8, 2012

SQL SERVER TO XML USING C#


In SSIS we dont have XML DESTINATION. The following c# code  is used to populate data from sql server to xml file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace XmlConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            string XmlPath=@"d:\article.xml";  \\ xml path
            string conn = @"Data source=SERVER;database=PublishingCompany;uid=sa;pwd=password"; \* sql connection string */
            SqlConnection SqlConn = new SqlConnection(conn);
            string query = "Select * from Article";
            SqlDataAdapter sda = new SqlDataAdapter(query, SqlConn);
            DataSet ds = new DataSet();
            sda.Fill(ds);

            DataTable dt = ds.Tables[0];
            ArrayList columns = new ArrayList();

            foreach (DataColumn dc in dt.Columns )
            {
                columns.Add (dc.ToString());
            }

            XmlTextWriter xr = new XmlTextWriter(XmlPath,Encoding .UTF8);
            xr.WriteStartDocument();
            xr.WriteStartElement("Article");

            for(int i=0; i<dt.Rows .Count ;i++)
            {
                xr.WriteStartElement("Article");
                for (int j = 0; j < dt.Columns.Count; j++)
                {                  
                    xr.WriteElementString(columns[j].ToString (), dt.Rows[i][j].ToString());                   
                }
                xr.WriteEndElement();
            }
            xr.WriteEndElement();
            xr.WriteEndDocument();
            xr.Close();
        }
    }
}