ASP.NET/C#/Web Service – Creating XML string output from SQL query result
Recently, I had a situation where one of our clients which use our web service wants to inquire service and obtain specific sets of transaction details over the interval of time. So, I have decided to bundle the result transaction details into xml formatted output. This approach has mainly following advantages.
- The output will be just a plain xml string which client could easily attain.
- Since the result is in xml format, client could easily customize it to accommodate requirement at their end.
There are various ways to achieve this. Let me explain one of the simplest ways to perform this.
1. Store the database query result into DataTable.
2. Create a DataSet and add the DataTables to the DataSet instance.
3. Output the DataSet representation in XML format.
Program:
using System;
using System.Data;
class Program
{
static DataTable dTable()
{
/* Create DataTable from using the following hard-coded value, but you can connect and fetch records from database to sql datareader and load the datareader to DataTable which is fairly simple.*/
// Create DataTable instance.
DataTable dt = new DataTable(“Chelsea”);
dt.Columns.Add(“Player”, typeof(string));
dt.Columns.Add(“Age”, typeof(string));
dt.Columns.Add(“Position”, typeof(string));
dt.Rows.Add(“Drogba”, “33″, “Striker”);
dt.Rows.Add(“Lampard”, “32″, “Midfield”);
dt.Rows.Add(“Terry”, “31″, “Defence”);
dt.Rows.Add(“Cech”, “29″, “Keeper”);
return dt;
}
static void Main()
{
// Create DataSet instance.
DataSet ds = new DataSet(“FootballClub”);
// Add new DataTable.
ds.Tables.Add(dTable());
// Write xml data.
string xmlStr = ds.GetXml();
Console.WriteLine(xmlStr);
}
}
Output:
<FootballClub>
<Chelsea>
<Player>Drogba</Player>
<Age>33</Age>
<Position>Striker</Position>
</Chelsea>
<Chelsea>
<Player>Lampard</Player>
<Age>32</Age>
<Position>Midfield</Position>
</Chelsea>
<Chelsea>
<Player>Terry</Player>
<Age>31</Age>
<Position>Defence</Position>
</Chelsea>
<Chelsea>
<Player>Cech</Player>
<Age>29</Age>
<Position>Keeper</Position>
</Chelsea>
</FootballClub>
Here you go !! Store this output in a string and customize the result as per your need.
Note: In web application such as asp.net client (haven’t tried in other’s) you’ll need to use Server.HtmlEncode(xmlStr) in order to render the result in original format.