Connect to Access and Write to XML

Northwind.mdb

 

 //Connect to Access Data source System.Data.Odbc.OdbcConnection conn=new System.Data.Odbc.OdbcConnection(); conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @" DSN=North;Mode=ReadWrite;Persist Security Info=False"; string earlierorderID = ""; try { //Execute SQL command conn.Open(); OdbcCommand thiscommand = conn.CreateCommand(); thiscommand.CommandText = @"SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, Customers.ContactName, Orders.OrderID, [Order Details].ProductID, ([Order Details].ProductID * [Order Details].UnitPrice * [Order Details].Quantity) * (1 - [Order Details].Discount) AS Expr1FROM Employees, Orders, Customers, [Order Details]WHERE Employees.EmployeeID = Orders.EmployeeID AND Orders.CustomerID = Customers.CustomerID AND Orders.OrderID = [Order Details].OrderIDORDER BY Employees.EmployeeID,Orders.OrderID"; OdbcDataReader dr = thiscommand.ExecuteReader(); //Create XML node string path = "C://NorthWind.xml"; XmlDocument xmlDoc = new XmlDocument(); XmlElement xmlRoot = xmlDoc.CreateElement("Orders"); xmlDoc.AppendChild(xmlRoot); while (dr.Read()) { if (!dr["OrderID"].ToString().Equals(earlierorderID)) { //If the orderID doesn't exist, build a Products node XmlElement order = xmlDoc.CreateElement("Order"); order.SetAttribute("ID", dr["OrderID"].ToString()); XmlElement employeeName = xmlDoc.CreateElement("EmployeeName"); employeeName.InnerText = dr["FirstName"].ToString() + dr["LastName"].ToString(); XmlElement customerName = xmlDoc.CreateElement("CustomerName"); customerName.InnerText = dr["ContactName"].ToString(); XmlElement products = xmlDoc.CreateElement("Products"); XmlElement productID = xmlDoc.CreateElement("ProductID"); productID.InnerText = dr["ProductID"].ToString(); products.AppendChild(productID); order.AppendChild(employeeName); order.AppendChild(customerName); order.AppendChild(products); xmlRoot.AppendChild(order); } else { //If OrderID already exists, add ProductID only var tmp = xmlRoot.LastChild.LastChild; XmlElement productID = xmlDoc.CreateElement("ProductID"); productID.InnerText = dr["ProductID"].ToString(); tmp.AppendChild(productID); } earlierorderID = dr["OrderID"].ToString(); } xmlDoc.Save(path); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); }