SQl server support native xml data. there are various benifits of storing xml data in sql sever such as
a) you can queries direcvtly against the XMl data
b) you can index the XMl data
c) you can eleiminate the falover issues and backup and restore issues that are associated with storing the XMl data separate and markup data.
In most cases structured data should be stored in relational tables whereas the xml data type is suitable for semi-structured and markup data.
To enable to take advantage of the xml data type in sql sever, ADO.Net provides classes
Accessing XMl data from SQl sever
the SqlXml class to help you work with the Xml data type. this class is defined in the System.Data.SqlTypes namespace.
you can access instances of the SqlXml class by using the DataReader object's GetSqlXml(0 method.
Consider the following SQL queries
create Table Students(Marks xml)
Insert Students values('80')
Insert Students values('85')
select * from Students
The preceding queries create a table named students, which has a column of the type xml.
you can retrieve the records stored in this table and dispay them in a DataGrid control by using SqlXml class and the GetSqlXml90 method. the getSqlXml() method gets the value of a Column of an XMl values.
The following code retrieve this information after a user clicks a Retrieve button and display them in a DataGrid control.
public partial class Form1 : Form
private void button1_Click(object sender, EventArgs e)
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog = your_database;uid =sa;pwd =your_password"))
using (SqlCommand cmd = con.CreateCommand())
cmd.CommandText = "Select * from Students";
using(SqlDataReader dreader = cmd.ExecuteReader())
SqlXml student = dreader.GetSqlXml(0);
dataGridView1.DataSource = ds;
In the preceding code, a connection is opened to the database and a query is executed to retrieve the details stored in the table. by using the SqlDataReader object, you can read each row. The getSqlXml() method retrieves the instance of the SqlXml class. As long as the SqlXml object is not null, the xml dara is read into the dataset object each SqlXml object that is read into the dataset appends to the existing XmlData. the resulting DataGrid object display a + sign indicating the existence of tables.
Reference Link :-