Convert Dataset to XML

Question

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.

score 30 · Accepted Answer · edited Feb 04 '16 at 05:25

30

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

edited Feb 04 '16 at 05:25

Chris Hammond

8,873
1
26
34

answered Dec 05 '11 at 10:42

Abdul Munim

18,869
8
52
61

Yes this works, but special characters appear as questionmarks, is there a way around this? – NomenNescio Dec 05 '11 at 10:51
That's probably because of ASCII encoding. Try with `Encoding.UTF8`. Updating code now – Abdul Munim Dec 05 '11 at 10:53
```ToArray()``` did you mean ```ToString()```? – Jay Croghan Jan 15 '20 at 03:54

score 16 · Answer 2 · edited Jan 04 '17 at 13:53

16

Simply use Dataset.getXml():

doc.LoadXml(ds.GetXml());

edited Jan 04 '17 at 13:53

shA.t

16,580
5
54
111

answered Jul 24 '12 at 14:28

Venkat

853
10
26

score 7 · Answer 3 · edited Jan 04 '17 at 13:54

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

score 3 · Answer 4 · answered Dec 05 '11 at 10:38

3

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

answered Dec 05 '11 at 10:38

Oded

489,969
99
883
1,009

score 1 · Answer 5 · edited Jan 04 '17 at 14:49

1

if ds is your dataset..

you can use:

ds.getXml();

this helps in getting XML

edited Jan 04 '17 at 14:49

Liam

27,717
28
128
190

answered Jun 17 '16 at 17:23

venkk

29
2

[Above answer](http://stackoverflow.com/a/11633050/4519059) is similar ;). – shA.t Jan 04 '17 at 14:44

score 0 · Answer 6 · answered Feb 03 '14 at 09:40

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

score 0 · Answer 7 · answered Aug 20 '18 at 09:39

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

        }

Convert Dataset to XML

7 Answers7

Linked