[Solved] How to generate an xml file from datatable [closed]

by


Before anybody gives me negative points for this highly unusual solution, please understand the problems.
1) The multiple Folder nested tags
2) The html text

I using a xml template file to read intial xml. This xml is complicated so I don’t think it is wise to create the xml from scratch.

Use the following xml template for main file

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2"
 xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:json="http://www.w3.org/2005/Json">
  <Folder>
    <name>***COUNTRY***</name>
    <open>1</open>
    <Folder>
      <name>***CITY TYPE***</name>
      <open>1</open>
      <Folder>
        <name>***SIZE***</name>
        <Folder>
          <name>***STATUS***</name>
          <Document>
            <name>POINTS</name>
            <StyleMap id="Licerio0_0">
              <Pair>
                <key>normal</key>
                <styleUrl>#Licerio0_0N</styleUrl>
              </Pair>
              <Pair>
                <key>highlight</key>
                <styleUrl>#Licerio0_0H</styleUrl>
              </Pair>
            </StyleMap>
            <Style id="Licerio0_0N">
              <IconStyle>
                <color>ff00ff00</color>
                <scale>0.4</scale>
                <Icon>
                  http://maps.google.com/mapfiles/kml/shapes/shaded_dot.png
                </Icon>
              </IconStyle>
              <LabelStyle>
                <color>ff00ff00</color>
                <scale>0.6</scale>
              </LabelStyle>
              <BalloonStyle>
                <text>$How to generate an xml file from datatable [closed]</text>
              </BalloonStyle>
              <LineStyle>
                <antialias>0</antialias>
              </LineStyle>
              <PolyStyle>
                <fill>0</fill>
                <outline>0</outline>
              </PolyStyle>
            </Style>
            <Placemark>
              <name>***CITY NAME***</name>
              <description>
                <![CDATA[<img src="http://upload.wikimedia.org/wikipedia/commons/***COUNTRY***.png"width="47" height="67">
  <br>RSID = <b>***CITY NAME***</b><ul>
   <li>PIN = ***PIN***
   <li>LONGITUDE = ***LONGITUDE***
   <li>LATITUDE = ***LATITUDE***
   <li>SCALE = ***SCALE***
   <li>TOTAL TILT = ***TILT***
   <li>CH = ***CH***
   <li>REGION = ***REGION***</ul></li>]]>
              </description>
              <styleUrl>#Licerio0_0</styleUrl>
              <Point>
                <gx:drawOrder>3</gx:drawOrder>
                <coordinates>***LONGITUDE***,***LATITUDE***,***SCALE***</coordinates>
              </Point>
            </Placemark>
          </Document>
        </Folder>
      </Folder>

    </Folder>
  </Folder>
</kml>

The use following template for 2nd file

<?xml version="1.0" encoding="utf-8" ?>
<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2"
 xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:json="http://www.w3.org/2005/Json">
  <name>LINES</name>
  <Style id="Zamoras0_0">
  </Style>
  <Placemark>
    <name>***Site Id***</name>
    <styleUrl>#Zamoras0_0</styleUrl>
    <MultiGeometry>
      <LineString>
        <gx:drawOrder>3</gx:drawOrder>
        <coordinates>***LONGITUDE***,***LATITUDE***,***SCALE***</coordinates>
      </LineString>
    </MultiGeometry>
  </Placemark>
</kml>

Then use code below

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using System.Xml.Linq;

namespace ConsoleApplication9
{
    class Program
    {
        const string FILENAME1 = @"c:\temp\test1.xml";
        const string FILENAME2 = @"c:\temp\test2.xml";
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("countryname", typeof(string));
            dt.Columns.Add("citytype", typeof(string));
            dt.Columns.Add("size", typeof(string));
            dt.Columns.Add("status", typeof(string));
            dt.Columns.Add("cityname", typeof(string));
            dt.Columns.Add("pin", typeof(int));
            dt.Columns.Add("LONGITUDE", typeof(double));
            dt.Columns.Add("LATITUDE", typeof(double));
            dt.Columns.Add("SCALE", typeof(double));
            dt.Columns.Add("TOTAL", typeof(int));
            dt.Columns.Add("TILT", typeof(string));
            dt.Columns.Add("CH", typeof(string));
            dt.Columns.Add("REGION", typeof(string));
            dt.Columns.Add("streets", typeof(string));

            dt.Rows.Add(new object[] { "USA", "Corperation", "LARGE", "WORKING", "newyork", 676306, 24.4251, 25.6011, 0.0, 87, "NEM", "", "", "firststreet" });
            dt.Rows.Add(new object[] { "USA", "Corperation", "LARGE", "WORKING", "newyork", 676306, 24.4251, 25.6011, 0.0, 87, "NEM", "", "", "downtownstreet" });
            dt.Rows.Add(new object[] { "USA", "Corperation", "LARGE", "WORKING", "chicago", 676306, 24.4251, 25.6011, 0.0, 87, "NEM", "", "", "chicstreet" });
            dt.Rows.Add(new object[] { "USA", "Corperation", "LARGE", "WORKING", "la", 676306, 24.4251, 25.6011, 0.0, 87, "NEM", "", "", "lastreet" });
            dt.Rows.Add(new object[] { "USA", "Corperation", "MEDIUM", "WORKING", "buffalo", 676306, 24.4251, 25.6011, 0.0, 87, "NEM", "", "", "buffalostreet" });
            dt.Rows.Add(new object[] { "USA", "Corperation", "MEDIUM", "WORKING", "buffalo", 676306, 24.4251, 25.6011, 0.0, 87, "NEM", "", "", "cowstreet" });
            dt.Rows.Add(new object[] { "USA", "Corperation", "MEDIUM", "WORKING", "denver", 676306, 24.4251, 25.6011, 0.0, 87, "NEM", "", "", "street" });
            dt.Rows.Add(new object[] { "USA", "Corperation", "MEDIUM", "NOT WORKING", "lasvegas", 676306, 24.4251, 25.6011, 0.0, 87, "NEM", "", "", "street" });
            dt.Rows.Add(new object[] { "USA", "Corperation", "SMALL", "WORKING", "trenton", 676306, 24.4251, 25.6011, 0.0, 87, "NEM", "", "", "street" });
            dt.Rows.Add(new object[] { "USA", "Village", "SMALL", "WORKING", "albany", 676306, 24.4251, 25.6011, 0.0, 87, "NEM", "", "", "street" });
            dt.Rows.Add(new object[] { "USA", "Village", "SMALL", "WORKING", "hartford", 676306, 24.4251, 25.6011, 0.0, 87, "NEM", "", "", "street" });
            dt.Rows.Add(new object[] { "SPAIN", "Corperation", "MEDIUM", "WORKING", "BARCELONA", 11111, 34.4251, 25.6011, 0.0, 82, "LEV" });
            dt.Rows.Add(new object[] { "ITALY", "Corperation", "LARGE", "WORKING", "ITALY", 21111, 14.4251, 15.6011, 0.0, 80, "MIR" });

            XDocument doc1 = XDocument.Load(FILENAME1);
            XElement kml = (XElement)doc1.FirstNode;
            XNamespace ns = kml.Name.Namespace;
            XElement document = doc1.Descendants().Where(x => x.Name.LocalName == "Document").FirstOrDefault();
            string documentStr = document.ToString();
            kml.RemoveAll();

            XDocument doc2 = XDocument.Load(FILENAME2);
            XElement linesDoc = (XElement)doc2.FirstNode;
            string linesStr = linesDoc.ToString();


            var countryRows = dt.AsEnumerable().GroupBy(x => x.Field<string>("countryname"));
            foreach (var country in countryRows)
            {
                XElement newCountry = new XElement(ns + "Folder", new object[] {
                       new XElement(ns + "name", country.Key),
                       new XElement(ns + "open", 1)
                });
                kml.Add(newCountry);

                var cityTypeRows = country.GroupBy(x => x.Field<string>("citytype"));
                foreach (var cityTypeRow in cityTypeRows)
                {
                    XElement newCityType = new XElement(ns + "Folder", new object[] {
                       new XElement(ns + "name", cityTypeRow.Key),
                       new XElement(ns + "open", 1)
                    });
                    newCountry.Add(newCityType);

                    var citySizeRows = cityTypeRow.GroupBy(x => x.Field<string>("size"));
                    foreach (var citySizeRow in citySizeRows)
                    {
                        XElement newCitySize = new XElement(ns + "Folder", new object[] {
                           new XElement(ns + "name", citySizeRow.Key)
                        });
                        newCityType.Add(newCitySize);

                        var cityStatusRows = cityTypeRow.GroupBy(x => x.Field<string>("status"));
                        foreach (var cityStatusRow in cityStatusRows)
                        {
                            XElement newStatus = new XElement(ns + "Folder", new object[] {
                               new XElement(ns + "name", cityStatusRow.Key)
                            });
                            newCitySize.Add(newStatus);

                            var cityRows = cityTypeRow.GroupBy(x => x.Field<string>("cityname"));
                            foreach (var city in cityRows)
                            {

                                string newDocumentStr = documentStr;
                                newDocumentStr = newDocumentStr.Replace("***CITY NAME***", city.Key);
                                newDocumentStr = newDocumentStr.Replace("***PIN***", city.FirstOrDefault().Field<int>("pin").ToString());
                                newDocumentStr = newDocumentStr.Replace("***LONGITUDE***", city.FirstOrDefault().Field<double>("LONGITUDE").ToString());
                                newDocumentStr = newDocumentStr.Replace("***LATITUDE***", city.FirstOrDefault().Field<double>("LATITUDE").ToString());
                                newDocumentStr = newDocumentStr.Replace("***SCALE***", city.FirstOrDefault().Field<double>("SCALE").ToString());
                                newDocumentStr = newDocumentStr.Replace("***TOTAL***", city.FirstOrDefault().Field<int>("TOTAL").ToString());
                                newDocumentStr = newDocumentStr.Replace("***TILT***", city.FirstOrDefault().Field<string>("TILT"));
                                newDocumentStr = newDocumentStr.Replace("***CH***", city.FirstOrDefault().Field<string>("CH"));
                                newDocumentStr = newDocumentStr.Replace("***REGION***", city.FirstOrDefault().Field<string>("REGION"));

                                XElement newCity = XElement.Parse(newDocumentStr);
                                newCitySize.Add(newCity);
                                foreach (var street in city)
                                {
                                    if (street.Field<object>("streets") != null)
                                    {
                                        string newlinesStr = linesStr;
                                        newlinesStr = newlinesStr.Replace("***LONGITUDE***", street.Field<double>("LONGITUDE").ToString());
                                        newlinesStr = newlinesStr.Replace("***LATITUDE***", street.Field<double>("LATITUDE").ToString());
                                        newlinesStr = newlinesStr.Replace("***SCALE***", street.Field<double>("SCALE").ToString());
                                        newlinesStr = newlinesStr.Replace("***Site Id***", street.Field<string>("streets").ToString());

                                        XElement newLine = XElement.Parse(newlinesStr);
                                        newCity.Add(newLine.Elements());
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

17

solved How to generate an xml file from datatable [closed]