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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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());

Method 2

Simply use Dataset.getXml():

doc.LoadXml(ds.GetXml());

Method 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();
}

Method 4

Use DataSet.WriteXml – it will output the dataset as XML.

Method 5

if ds is your dataset..

you can use:

ds.getXml();

this helps in getting XML

Method 6

We can use this also

 
  Private Function DsToXML(DataSet ds) as System.Xml.XmlDataDocument

    Dim xmlDoc As System.Xml.XmlDataDocument 
    Dim xmlDec As System.Xml.XmlDeclaration
    Dim xmlWriter As System.Xml.XmlWriter
    xmlWriter = New XmlTextWriter(context.Response.OutputStream,System.Text.Encoding.UTF8)

    xmlDoc = New System.Xml.XmlDataDocument(ds)
    xmlDoc.DataSet.EnforceConstraints = False
    xmlDec = xmlDoc.CreateXmlDeclaration("1.0", "UTF-8", Nothing)
    xmlDoc.PrependChild(xmlDec)
    xmlDoc.WriteTo(xmlWriter)
    Retuen xmlDoc
  End Eunction

Method 7

Try this. It worked for me.

static XmlDocument xdoc = new XmlDocument(); //static; since i had to access this file someother place too
protected void CreateXmlFile(DataSet ds) 
    {
      //ds contains sales details in this code; i.e list of products along with quantity and unit
      //You may change attribute acc to your needs ; i.e employee details in the below code

        string salemastid = lbl_salemastid.Text;
        int i = 0, j=0;
        String str = "salemastid:" + salemastid;
        DataTable dt = ds.Tables[0];
        string xml = "<Orders>" ;
         while (j < dt.Rows.Count)
         {
             int slno = j + 1;
             string sl = slno.ToString();
             xml += "<SlNo>" + sl +"</SlNo>" +
                       "<PdtName>" + dt.Rows[j][0].ToString() + "</PdtName>" +
                       "<Unit>" + dt.Rows[j][1].ToString() + "</Unit>" +
                       "<Qty>" + dt.Rows[j][2].ToString() + "</Qty>";
             j++;
         }
         xml += "</Orders>";

         xdoc.LoadXml(xml);
         //Here the xml is prepared and loaded in xml DOM.

         xdoc.Save(Server.MapPath("Newsales.xml"));
         //You may also use some other names instead of 'Newsales.xml'

         //to get a downloadable file use the below code
         System.IO.MemoryStream stream = new System.IO.MemoryStream();
                XmlTextWriter xwriter = new XmlTextWriter(stream, System.Text.Encoding.UTF8);

                xdoc.WriteTo(xwriter);
                xwriter.Flush();
                Response.Clear();
                Encoding.UTF8.GetString(stream.ToArray());
                byte[] byteArray = stream.ToArray();
                Response.AppendHeader("Content-Disposition", "filename=OrderRequest.xml");
                Response.AppendHeader("Content-Length", byteArray.Length.ToString());
                Response.ContentType = "application/octet-stream";
                Response.BinaryWrite(byteArray);
                xwriter.Close();
                stream.Close();

        }

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0