In continuation to Part I of this article, we will examine how we can load the XML Fragment returned by the SQL FOR XML query, into a DataSet, to form a Well Formed XML Document.
SqlConnection objCon = new SqlConnection("Connection String");
SqlCommand objCmd = new SqlCommand("select * from customers for xml auto", objCon);
XmlReader xr = null;
XmlDocument xDoc = new XmlDocument();
DataSet ds = new DataSet();
try
{
objCon.Open();
xr = objCmd.ExecuteXmlReader();
ds.ReadXml(xr);
xDoc.LoadXml(ds.GetXml());
xDoc.Save(Server.MapPath("Test.xml"));
}
catch(Exception ex)
{
Response.Write(ex.Message);
}
finally
{
objCon.Close();
xr.Close();
}
As we can see the above code uses a DataSet to load the XML Fragment returned by the FOR XML Select query. The DataSet object is intelligent enough to append a ROOT Node to all XML Data that is being read into/written by it and hence the error "This document already has a DocumentElement node." can be avoided.
We will examine the other approaches in the next articles.
Read Part III, Part IV of the series.
Cheers and Happy Programming !!!
posted @ Monday, September 19, 2005 9:02 AM