Nasted XML from DataSet with unrelated tables

[majkinetor]

Hello

I have 3 unrelated tables in the DataSet, tables X, H1, H2.
There is nothing like primary keys or relations in them. X have N records, while H1&H2 have only 1 record.

I wont to export this DataSet in the following format

<DataSetName>
    <H1>
     ....
    
         <H2>
          ...
          </H2>
    </H1>
    
    <X1>
    </X1>
    
    <X2>
    </X2>
     ...


     ...
     <Xn>
     </Xn>
</DataSetName>

The problem is nasted part. Is there any way to set H2 to be nasted table of H1. I saw that i can impose relation at MSDN but if I do so, resulting XML will have "relation key" present in both tables (H1 & H2) so I will have to scan the resulting file afterwards to fix that. I am wondering is there any solution that can connect H2 to H1 so that WriteXML can do it without duplicating relation keys (or even writting them)

ALong with that, low priority question, is there any way to map some H1 table column as an attribute rather then element. For instance, I want first column to be added as attribut <H1 A=col_val>

Thx in advance.

 

[jmeckley]

the output result you want is not a dataset, it's simply xml. this means you'll need to design a routine to compile the data your self. here is a quick console app i threw together which solve your issue:

using System;
using System.Data;
using System.Xml;
using System.IO;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable table1 = new DataTable("Table1");
            table1.Columns.AddRange(new DataColumn[] { 
                new DataColumn("Col1"), 
                new DataColumn("Col2") });
            table1.Rows.Add(new object[] { "row 1", "row 2" });

            DataTable table2 = new DataTable("Table2");
            table2.Columns.AddRange(new DataColumn[] { 
                new DataColumn("Col1"), 
                new DataColumn("Col2"), 
                new DataColumn("Col3") });
            table2.Rows.Add(new object[] { "row 1", "row 2", "row 3" });

            DataTable table3 = new DataTable("Table3");
            table3.Columns.AddRange(new DataColumn[] { 
                new DataColumn("Col1"), 
                new DataColumn("Col2"), 
                new DataColumn("Col3"), 
                new DataColumn("Col4") });
            table3.Rows.Add(new object[] { "row 1", "row 2", "row 3", "row 4" });
            table3.Rows.Add(new object[] { "row 2", "row 2", "row 3", "row 4" });

            DataTable table4 = new DataTable("Table4");
            table4.Columns.AddRange(new DataColumn[] { new DataColumn("Col1") });
            table4.Rows.Add(new object[] { "row 1" });
            table4.Rows.Add(new object[] { "row 2" });
            table4.Rows.Add(new object[] { "row 3" });

            DataSet ds = new DataSet("MyDataSet");
            ds.Tables.AddRange(new DataTable[] { table3, table4 });
            
            MyDataSetExporter exporter = new MyDataSetExporter(table1, table2, ds);
            exporter.CompileDataTables();
            exporter.ExportToFile(@"c:\text.xml", true);

            Console.ReadKey();
        }
    }

    class MyDataSetExporter
    {
        private XmlDocument document;
        private DataTable h1, h2;
        private DataSet data;

        public DataTable H1
        {
            get { return this.h1; }
            set { this.h1 = value; }
        }
        public DataTable H2
        {
            get { return this.h2; }
            set { this.h2 = value; }
        }
        public DataSet Data
        {
            get { return this.data; }
            set { this.data = value; }
        }

        public MyDataSetExporter() : this(new DataTable(), new DataTable(), new DataSet()) { }
        public MyDataSetExporter(DataTable H1, DataTable H2) : this(H1, H2, new DataSet()) { }
        public MyDataSetExporter(DataTable H1, DataTable H2, DataTable Data) : this (H1, H2, new DataSet()) 
        {
            this.Data.Tables.Add(Data);
        }
        public MyDataSetExporter(DataTable H1, DataTable H2, DataSet Data)
        {
            this.H1 = H1;
            this.H2 = H2;
            this.Data = Data;
            this.document = new XmlDocument();
        }

        public void CompileDataTables()
        {
            this.document.LoadXml("<RootElement />");
            this.document.DocumentElement.AppendChild(this.ConvertDataTableToXmlNode(this.H1));
            this.document.DocumentElement.ChildNodes[0].AppendChild(this.ConvertDataTableToXmlNode(this.H2));
            foreach (DataTable table in this.Data.Tables)
            {
                this.document.DocumentElement.AppendChild(this.ConvertDataTableToXmlNode(table));
            }
        }
        public void ExportToFile(string filename, bool forceSave)
        {
            FileInfo file = new FileInfo(filename);
            if (!file.Directory.Exists)
            {
                Directory.CreateDirectory(file.Directory.FullName);
            }
            else if (file.Exists && !forceSave)
            {
                throw new System.IO.IOException("File already exists. cannot overwrite.");
            }

            this.document.Save(filename);
        }

        private XmlNode ConvertDataTableToXmlNode(DataTable table)
        {
            XmlNode toReturn = this.document.CreateNode(XmlNodeType.Element, table.TableName, string.Empty);
            XmlNode node;
            foreach (DataRow row in table.Rows)
            {
                foreach (DataColumn column in table.Columns)
                {
                    if (column.Ordinal == 0)
                    {
                        toReturn.Attributes.Append(this.document.CreateAttribute(column.ColumnName));
                        toReturn.Attributes[0].Value = (string)row[column];
                    }
                    else
                    {
                        node = this.document.CreateNode(XmlNodeType.Element, column.ColumnName, string.Empty);
                        node.InnerText = (string)row[column];
                        toReturn.AppendChild(node);
                    }
                }
            }

            return toReturn;
        }
    }
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[majkinetor]

Thank you very much jason.