SQL To XML

Certain times we need to export our grid data or our custom query data to xml format.
.Net provides a very easy way to that , The two data object i.e. Dataset and DataTable has built-in overloads to do this.

public void WriteSQLToXML()
        {
 using (SqlConnection con = new SqlConnection(“server=your_sql_server_name;Database=your_db_name;Integrated Security=true;”))
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
using (SqlCommand cm = new SqlCommand(“Select * from your_table_name” , con))
                {
using (SqlDataAdapter DA = new SqlDataAdapter(cm))
                    {
                        DataSet DS = new DataSet(“mytable”);
                        DA.Fill(DS, “mytable”);


    //for winforms
   string _path = Application.StartupPath + “myData.xml”;


   //for webform
   string _path = Server.MapPath(“”) + “\\myData.xml”;


//it will save myData.xml to your local root directory of your application
                       
DS.WriteXml(_path);
                    }
                }
                
            }
        }

Ranking-Query

While working with sql queries , sometimes we need to sort result set and find out the top 10 products , prices etc . we use order by clause to sort the results either ascending or descending.
But what if someone told u to find out the top 10 records and display their ranks too besides.
Here is one demonstration of ranking query . SQL Server provides a special function rank() over that can solve the problem very easily and its is very useful .

Syntax : RANK () OVER ( < order_by_clause > )

For example if in sample DB “Adventure work” we want to find top 10 products according to unit price then we can use the query as

Select Distinct ProductID, UnitPrice , LineTotal, ReceivedQty,RANK() OVER (order by UnitPrice) as [Unit Price Rank]
from Purchasing.PurchaseOrderDetail
Where ProductID between 800 and 900
order by [Unit Price Rank]

It will return all products sorted alongwith their rank like