Convert Dataset to XML
I've been stuck with this problem for a few hours and can't seem to figure it out, so I'm asking here :)
Alright, I've got this function:
private void XmlDump()
{
XDocument doc = new XDocument(new XDeclaration("1.0", "utf-8", "yes"));
XElement rootElement = new XElement("dump");
rootElement.Add(TableToX("Support"));
string connectionString = ConfigurationManager.ConnectionStrings["MyDb"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
string sql = "select * from support";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet("Test");
da.Fill(ds, "support");
// Convert dataset to XML here
var docresult = // Converted XML
Response.Write(docResult);
Response.ContentType = "text/xml; charset=utf-8";
Response.AddHeader("Content-Disposition", "attachment; filename=test.xml");
Response.End();
}
I've been trying all kind of different things but I keep getting errors, so I've left the how to convert DataSet to XML part blank.
And another thing, this query contains columns with special characters.
Solution 1:
You can use ds.WriteXml
, but that will require you to have a Stream
to put the output into. If you want the output in a string, try this extension method:
public static class Extensions
{
public static string ToXml(this DataSet ds)
{
using (var memoryStream = new MemoryStream())
{
using (TextWriter streamWriter = new StreamWriter(memoryStream))
{
var xmlSerializer = new XmlSerializer(typeof(DataSet));
xmlSerializer.Serialize(streamWriter, ds);
return Encoding.UTF8.GetString(memoryStream.ToArray());
}
}
}
}
USAGE:
var xmlString = ds.ToXml();
// OR
Response.Write(ds.ToXml());
Solution 2:
Simply use Dataset.getXml()
:
doc.LoadXml(ds.GetXml());
Solution 3:
Write like below code part
DataTable dt = new DataTable("MyData");
dt.WriteXml(@Application.StartupPath + "\\DataBaseValues.xml");
Or, You can convert directly the dataSet
also as said by Oded like,
private void WriteXmlToFile(DataSet thisDataSet)
{
if (thisDataSet == null)
{
return;
}
// Create a file name to write to.
string filename = "myXmlDoc.xml";
// Create the FileStream to write with.
System.IO.FileStream myFileStream = new System.IO.FileStream(filename, System.IO.FileMode.Create);
// Create an XmlTextWriter with the fileStream.
System.Xml.XmlTextWriter myXmlWriter =
new System.Xml.XmlTextWriter(myFileStream, System.Text.Encoding.Unicode);
// Write to the file with the WriteXml method.
thisDataSet.WriteXml(myXmlWriter);
myXmlWriter.Close();
}
Solution 4:
Use DataSet.WriteXml
- it will output the dataset as XML.