C#
C#

Create customised xml from dataset c#

Hi,
I have a select query like this

Select value,key,id from table;
Which returns the result like this
value key id
a 101 1
b 102 1
a 103 2
a 104 2

actully the resultset is in dataset i need to manipulate the dataset and generate the xml
like this
How to generate the xml please help

<AvailableFields>
<Table Name="table1">
<Type Name="a">
<Fields Key="101" id="1"/>
<Fields Key="103" id="2" />
<Fields Key="104" id="2" />
</Type>
<Type Name="b">
<Fields Key="103" id="1"/>
</Type>
</Table>
</AvailableFields>

Regards,
nanban.

nanbanAsked: 2006-11-14

Who is Participating?

LVL 15

ozymandiasCommented: 2006-11-14

                 XmlDocument doc = new XmlDocument();
                  doc.LoadXml("<AvailableFields><Table Name=\"table1\"></Table></AvailableFields>");
                  XmlNode tableNode = doc.DocumentElement.ChildNodes[0];

                  DataSet data = new DataSet();
                  DataTable table = new DataTable("table1");
                  data.Tables.Add(table);
                  table.Columns.Add(new DataColumn("value", typeof(string)));
                  table.Columns.Add(new DataColumn("key", typeof(int)));
                  table.Columns.Add(new DataColumn("id", typeof(int)));

                  DataRow newRow = table.NewRow();
                  newRow.ItemArray = new object[]{"a", 101, 1};
                  table.Rows.Add(newRow);

                  newRow = table.NewRow();
                  newRow.ItemArray = new object[]{"b", 102, 1};
                  table.Rows.Add(newRow);

                  newRow = table.NewRow();
                  newRow.ItemArray = new object[]{"a", 103, 2};
                  table.Rows.Add(newRow);

                  newRow = table.NewRow();
                  newRow.ItemArray = new object[]{"a", 104, 2};
                  table.Rows.Add(newRow);

                  foreach(DataRow row in data.Tables[0].Rows){
                        string type = row["value"].ToString();
                        string key = row["key"].ToString();
                        string id = row["id"].ToString();

                        XmlNode typeNode = tableNode.SelectSingleNode("Type[@Name = '" + type + "']");
                        if (typeNode == null){
                              typeNode = doc.CreateElement("Type");
                              ((XmlElement)typeNode).SetAttribute("Name", type);
                              tableNode.AppendChild(typeNode);

                        }
                        XmlElement newField = doc.CreateElement("Fields");
                        newField.SetAttribute("Key", key);
                        newField.SetAttribute("id", id);
                        typeNode.AppendChild(newField);
                  }

                  Console.WriteLine(doc.OuterXml);
                  Console.ReadLine();

LVL 8

sabeeshCommented: 2006-11-14

DataTable dtXmlLink = new DataTable("XmlLink");
dtXmlLink.Columns.Add("Key",typeof(int));
dtXmlLink.Columns.Add("Id",typeof(string));
--Loop---
DataRow drXmlLink = dtXmlLink.NewRow();
drXmlLink["Key"]= "";// loop thourgh and add values in to datatable
drXmlLink["Id"]= "";
dtXmlLink.Rows.Add(drXmlLink);
---end loop---

                  {
                        DataSet ds = new DataSet();
                        ds.Tables.Add(dtXmlLink );
                        MemoryStream memStream = new MemoryStream();
                        StreamReader streamReader = new StreamReader(memStream);
                        ds.WriteXml(memStream, XmlWriteMode.IgnoreSchema);
                        memStream.Seek(0, SeekOrigin.Begin);
                        xmlString = streamReader.ReadToEnd();
                  }