To write values from SQL Server table to new XML file

Suppose we have a table “Test”.
Then we can copy its values to a xml file , let it be “Test.xml”.

and Follow the following code..

Populating a SQL Server Table with data from XML

Writing values from XML file to a SQL server table.
Our xml file be “Test.xml”



Add the using directive
Using System.Data.SqlClient;
Then write the following code:



Here I am using the sql server express as the database.And the database name is “db2”.
For copying data from xml to sql server table here I am using SqlBulkCopy.
For this you should have same field names in both the cases, but if you have different names then we can map them as follows
bkc.ColumnMappings.Add("Sr_no", "SerialNum");
bkc.ColumnMappings.Add("Name", "FirstName");
bkc.ColumnMappings.Add("City", "City")

where bkc.ColumnMappings.Add(xmlfield,tablefield)
and the column names are case sensitive. i.e if you type”city” instead of “City” then it will throw runtime error “The given ColumnMapping does not match up with any column in the source or destination.”

Populating Gridview with XML data

Let the needed xml file be “Test.xml”. It is placed in “App_Data” folder of the solution.
Test.xml


Now add a gridview “GridView1’ to the webform.
And in the code behind add the following code ..

protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds.ReadXml(Request.PhysicalApplicationPath + @"App_Data\Test.xml");
GridView1.DataSource = ds;
GridView1.DataBind();
}