After seeing poor implementation of this I decided it would be a good idea to make a simple guide available.
First we will start by declaring the xmlDocument and what will be its root element
XmlDocument xmlDoc = new XmlDocument();
XmlElement root = xmlDoc.CreateElement("root");
Next we will jump over to the SQL command that will be used to get your data. Obviously this query below is just an example, make sure yours is relevant.
string sqlQuery = "SELECT col1, col2, col3 From myTable Where col3 <> ''";
Declare your SqlConnection. I suggest using your web.config to set this up. Technically you can do this multiple ways, but here are a couple.
1.
In web.config:
<appSettings>
<add key="sqlConnection" value="Data Source=192.168.0.1;Initial Catalog=myDB;User ID=myUser;Password=myPassword;"/>
</appSettings>
Then use the key to build the connection.
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["sqlConnection"]);
Or option 2, which is considered to be more proper:
In web.config:
<connectionStrings>
<add name="sqlConnection" connectionString="server=192.168.0.1;uid=myUser;pwd=myPassword;database=myDB" providerName="System.Data.SqlClient" />
</connectionStrings>
Then using the connection string to build the connection:
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConnection"].ConnectionString);
Honestly, I'm currently using option 1 for the application that brought this to mind. Mostly because I'm keeping my new connections in the same format used by the prior developer, while I work on more important changes.
After you declare your connection, start up a try/catch block, since we are going to open up the connection. Always a good thing to be safe around connections.
try{
conn.Open();
Once these are declared we are going to
Now it is time to start reading the sql data and giving the value to the nodes and attribute
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(sqlQuery, conn);
myReader = myCommand.ExecuteReader();
while(myReader.Read())
{
Now we want to declare the elements to be used in the xml. For the example I am going to do 1 row node with a child col node that has an attribute.
XmlNode row= xmlDoc.CreateElement("row");
XmlNode col1 = xmlDoc.CreateElement("col");
XmlAttribute col2 = xmlDoc.CreateElement("attribute");
After the nodes are declared we can assign the values
//attribute value assignment
col2.value = myReader["col2"].ToString();
//node value assignment
col1.InnerText = myReader["col1"].ToString();
Then once you have your nodes containing the proper values, you can append them
//Append the Attribute to col1
col1.Attributes.Append(col2);
//Append col1 to the row
row.AppendChild(col1);
//Append the row to the root
root.AppendChild(row);
Now we can close up the while loop and try/catch
}
}catch(Exception ex){
//do something if you want
Response.Write(ex.ToString());
}
Then we can append the root to the xml document
xmlDoc.AppendChild(root);
Now you sucessfully have an xml document with values from the sql table. At this point you can use the document or save it using xmlDoc.save(path);
Here is the resulting format of your xml:
<root>
<row>
<col attribute='Col2 data'>Col1 data</col>
</row>
</root>
Full Code:
using System;
using System.Xml;
using System.Data.SqlClient;
public class SqlToXml
{
public XmlDocument getSqlToXml{
XmlDocument xmlDoc = new XmlDocument();
XmlElement root = xmlDoc.CreateElement("root");
string sqlQuery = "SELECT col1, col2, col3 From myTable Where col3 <> ''";
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["sqlConnection"]);
try{
conn.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(sqlQuery, conn);
myReader = myCommand.ExecuteReader();
while(myReader.Read())
{
XmlNode row= xmlDoc.CreateElement("row");
XmlNode col1 = xmlDoc.CreateElement("col");
XmlAttribute col2 = xmlDoc.CreateElement("attribute");
col2.value = myReader["col2"].ToString();
col1.InnerText = myReader["col1"].ToString();
col1.Attributes.Append(col2);
row.AppendChild(col1);
root.AppendChild(row);
}
}catch(Exception ex){
//do something if you want
Response.Write(ex.ToString());
}
xmlDoc.AppendChild(root);
return xmlDoc;
}
}
Share on Facebook
Tweet Me
