.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal

Home >> Articles >> XML >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Processing XML data in SQL server

Posted By:Ravi Ranjan Kumar       Posted Date: October 31, 2011    Points: 200    Category: XML    URL: http://www.dotnetspark.com  

This article enable you to learn how to processing XML data in SQl server using C#.

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('8090')
Insert Students values('85100')
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. 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlTypes;
using System.Data.SqlClient;

namespace XmlData
    public partial class Form1 : Form
        public Form1()

        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 :- 
 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend