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();
}
}
}
No comments:
Post a Comment