sam sinfield | 
04 11th, 2007| 
I’ve been really slow in making a first post but I thought I would post this code snippet as it seems to be something I’m using quite a lot at the moment. It is for building xml from a database:
System.Xml.XmlDocument xmldoc = new System.Xml.XmlDocument();
xmldoc.LoadXml("");
System.Xml.XmlElement root = xmldoc.DocumentElement;
try
{
SqlConnection conn = new SqlConnection(connectionstring);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlSelectStatement;
using (SqlDataReader dr = cmd.ExecuteReader())
{
// Process rows
while (dr.Read())
{
System.Xml.XmlElement row = xmldoc.CreateElement("row");
for(int i=0;i
{
System.Xml.XmlElement el = xmldoc.CreateElement(dr.GetName(i));
if(!dr.IsDBNull(i))
{
el.InnerText = dr.GetValue(i).ToString();
}
row.AppendChild(el);
}
root.AppendChild(row);
}
}
conn.Close();
}
catch(){}
There is another way of doing it directly into xml using cmd.ExecuteXmlReader and putting ” for xml auto” on the end of your sql select statement but apparently this only works in sqlserver 200 or higher and it puts all the fields as attributes of a node with the name of the table and no root node which I didn’t like, personal choice.
I’m sure this has its problems, let me know you think