Processing XML Documents in C#
By Mesbah U. Ahmed

The major objectives of this article are to demonstrate how to write an XML document from a database query, and subsequently, how to read an XML document using C#? Obviously, we will have to use the ADO.Net to connect to the database. The Dot.Net DataSet class has two convenient methods to read and write XML documents. These are: DataSet.WriteXml(fileStream), and DataSet.ReadXml(fileStream). The WriteXml method generates the XML document including the MS XSD schema at the top.

Files to download:
ReadXML.cs Download
WriteXML.cs Download

1. How to write an XML document from a database query?

The following code will write an XML document named myXMLdata.xml in the directory of the source file. In this example, we want to run a SQL query to the Products table of the SQL Server 7.0 Northwind database, and construct an XML document from the results of the query. At first we need to include two Namespaces: the System.Data for using the ADO.Net classes, and the System.Data.SQL for using the SQL Server managed provider. The System.IO and the System.Text namespaces are used for the FileStream and StringBuilder classes, respectively.

Building the SQL string is a common task in database applications. Since the String objects are immutable (that means, it cannot be copied or modified at the same memory location), a StringBuffer object is more desirable while extending long strings in several steps. Hence, a StringBuilder object has been used to build the SQL string. The remainder of the code is self-explanatory.

// MyDotNet\myCSharp\XML\ WriteXML.cs
// Author: Mesbah U. Ahmed
using System;
using System.Data;// for ADO.Net
using System.Data.SQL;	 // for SQL Server managed provider
using System.IO;	     // for FileStream class
using System.Text;	     // for StringBuilder class
public class WriteXML
{ public static void Main()
  {     // Build a connection
         SQLConnection myConn = 
         new SQLConnection
         ("server=yourServerName;uid=yourUserId;pwd=yourPassword;database=Northwind");
      // Build the SQL string
      StringBuilder mySql = new StringBuilder("SELECT ProductId, ");
      mySql.Append("ProductName, UnitPrice ");
      mySql.Append("FROM Products ");
      mySql.Append("WHERE UnitPrice < 10.00");
      mySql.Append("ORDER BY UnitPrice "); 
      Console.WriteLine(mySql);

      // Build the DataSet Command object
      SQLDataSetCommand myCmd = new SQLDataSetCommand
                                                            (mySql.ToString(), myConn);
       // Build the DataSet     
      DataSet myDs = new DataSet();

      // Fill the DataSet with a dataset table named "Products"
      myCmd.FillDataSet(myDs, "Products");

      // Get a FileStream object
      FileStream myFs = 
          new FileStream
             ("myXmlData.xml",FileMode.OpenOrCreate,FileAccess.Write);

      // Use the WriteXml method of DataSet object to write an XML file from the DataSet
      myDs.WriteXml(myFs);
      myFs.Close();
      Console.WriteLine("File Written");
  }
}

// MyDotNet\myCSharp\XML\ReadXML.cs
// Author: Mesbah U. Ahmed
using System;
using System.Data;
using System.Data.SQL;
using System.IO;

public class ReadXML
{ public static void Main()
  {   
      // Get a DataSet object
      DataSet myDs = new DataSet();
      
      // Get a FileStream object.
      FileStream myFs = 
          new FileStream
             ("myXmlData.xml",FileMode.Open,FileAccess.Read);
      // Apply the ReadXml(fileSteeam method) to read the file
      myDs.ReadXml(myFs);
      
      // Get a DataTable object from the first table of the DataSet
      DataTable myDt = myDs.Tables[0];
      
      // Display the values for each row
      foreach (DataRow r in myDt.Rows)
         Console.WriteLine("{0} : {1} : ${2:f2}",
             r["ProductId"], r["ProductName"], 
                 Double.Parse(r["UnitPrice"].ToString()));
      myFs.Close();
  }
}