Create XML with DataTable.WriteXML but with additional Elements?

Question

What you see on the left is a result of a simple DataTable.WriteXML() call. What you see on the left is a predetermined schema I need to match. I am filling the datatable from a SQL database (please ignore that I am currently not including the ItemType element). How can I create the first several elements higher in the tree and then write the XML from the datatable?

EDIT: Another question comes to mind. Why is there a root element called Document Element and how do I either control what that is called or eliminate it?

EDIT: This is my code attempt thus far.

XmlWriter writer;
XmlWriterSettings xwSettings = new XmlWriterSettings();

xwSettings.Encoding = Encoding.UTF8;
xwSettings.Indent = true;

writer = XmlWriter.Create("c:\\Armanino\\Exigo\\TestItems.xml", xwSettings);

writer.WriteStartElement("GreatPlainsIntegration");
writer.WriteStartElement("TransmissionDate");
writer.WriteString(string.Format("{0:yyyy-M-d}", DateTime.Now));
writer.WriteEndElement();

writer.WriteStartElement("Batch");
writer.WriteStartElement("BatchSource");
writer.WriteString("Inv Mstr");
writer.WriteEndElement();

dtItems.WriteXml(writer);

writer.WriteEndElement();

writer.Close();

It results in this file:

I tried to create the xmlns:integration portion of the GreatPlainsIntegration element using writer.WriteAttributeString(), however, it complains about the ":".

EDIT: My Implemented Solution : Includes Additional Children to the Batch element and a second Batch element.

XDocument xd = XDocument.Parse(GetItemHeader());
XElement gpIntegration = (XElement)xd.FirstNode;

gpIntegration.Add(new object[] 
{
    new XElement("TransmissionDate", DateTime.Now.ToString("yyyy-M-d")), 
    new XElement("TransmissionTime", DateTime.Now.ToString("HH:mm")), 
    new XElement("Batch", new object[]
    {
        new XElement("TargetCatalog", dtItemsLWI.Rows[0].Field<string>(0)),
        new XElement("BatchSource", "Inv Mstr"),
        new XElement("BatchNumber", GetBatchName()), 
        new XElement("BatchComment", GetBatchComment())
    }), 
    new XElement("Batch", new object[]
    {
        new XElement("TargetCatalog", dtItemsLWE.Rows[0].Field<string>(0)),
        new XElement("BatchSource", "Inv Mstr"), 
        new XElement("BatchNumber", GetBatchName()),
        new XElement("BatchComment", GetBatchComment())
    })
});

XElement batchElementLWI = gpIntegration.Descendants("Batch").FirstOrDefault();
foreach (DataRow row in dtItemsLWI.Rows)
{
    batchElementLWI.Add(new XElement("Item", new XElement[]
    {
        new XElement(dtItemsLWI.Columns[1].ColumnName, row.Field<string>(1)), 
        new XElement(dtItemsLWI.Columns[2].ColumnName, row.Field<string>(2)),
        new XElement(dtItemsLWI.Columns[3].ColumnName, row.Field<string>(3)), 
        new XElement(dtItemsLWI.Columns[4].ColumnName, row.Field<string>(4))
    }));
}

DataTable dtItemSitesLWI = new DataTable();
dtItemSitesLWI.TableName = "ItemSite";

dtItemSitesLWI.Columns.Add("LegalEntityCode", typeof(string));
dtItemSitesLWI.Columns.Add("ItemNumber", typeof(string));
dtItemSitesLWI.Columns.Add("LocationCode", typeof(string));

da.GetItemSitesLWI(ref dtItemSitesLWI, sqlConn);

foreach (DataRow row in dtItemSitesLWI.Rows)
{
    batchElementLWI.Add(new XElement("ItemSite", new XElement[]
    {
        new XElement(dtItemSitesLWI.Columns[1].ColumnName, row.Field<string>(1)),
        new XElement(dtItemSitesLWI.Columns[2].ColumnName, row.Field<string>(2))
    }));
}

XElement batchElementLWE = gpIntegration.Descendants("Batch").Skip(1).FirstOrDefault();
foreach (DataRow row in dtItemsLWE.Rows)
{
    batchElementLWE.Add(new XElement("Item", new XElement[]
    {
        new XElement(dtItemsLWE.Columns[1].ColumnName, row.Field<string>(1)), 
        new XElement(dtItemsLWE.Columns[2].ColumnName, row.Field<string>(2)),
        new XElement(dtItemsLWE.Columns[3].ColumnName, row.Field<string>(3)), 
        new XElement(dtItemsLWE.Columns[4].ColumnName, row.Field<string>(4))
    }));
}

DataTable dtItemSitesLWE = new DataTable();
dtItemSitesLWE.TableName = "ItemSite";

dtItemSitesLWE.Columns.Add("LegalEntityCode", typeof(string));
dtItemSitesLWE.Columns.Add("ItemNumber", typeof(string));
dtItemSitesLWE.Columns.Add("LocationCode", typeof(string));

da.GetItemSitesLWE(ref dtItemSitesLWE, sqlConn);

foreach (DataRow row in dtItemSitesLWE.Rows)
{
    batchElementLWE.Add(new XElement("ItemSite", new XElement[]
    {
        new XElement(dtItemSitesLWE.Columns[1].ColumnName, row.Field<string>(1)),
        new XElement(dtItemSitesLWE.Columns[2].ColumnName, row.Field<string>(2))
    }));
}

XmlWriter writer;
XmlWriterSettings xwSettings = new XmlWriterSettings();

xwSettings.Encoding = Encoding.UTF8;
xwSettings.Indent = true;

writer = XmlWriter.Create("c:\\Armanino\\Exigo\\TestItems.xml", xwSettings);

xd.WriteTo(writer);

writer.Close();

Answer 1

Try XML Linq

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Item> items = new List<Item>() {
                new Item() { itemNumber = "PHAN-PHN-001", itemDescription = "Standard Phone Package", itemClass = "RETAIL"},
                new Item() { itemNumber = "OM0325", itemDescription = "Dual Basic Headset", itemClass = "CATALOG"},
                new Item() { itemNumber = "OM01373", itemDescription = "Light Cordless 1", itemClass = "CATALOG"}
            };

            string header = "<?xml version=\"1.0\" encoding= \"utf-8\" ?>" +
                "<GreatPlainIntegration></GreatPlainIntegration>";

            XDocument doc = XDocument.Parse(header);
            XElement greatPlainsIntegration = (XElement)doc.FirstNode;

            greatPlainsIntegration.Add(new object[] {
                new XElement("TransmissionDate", DateTime.Now.ToString("yyyy-M-d")),
                new XElement("Batch", new object[] {
                    new XElement("BatchSource", "Inv Mstr"),
                    new XElement("DocumentElement")
                })
            });
            XElement documentElement = greatPlainsIntegration.Descendants("DocumentElement").FirstOrDefault();
            documentElement.Add(items.Select(x => new XElement("Item", new XElement[] {
                new XElement("ItemNumber", x.itemNumber),
                new XElement("ItemDescription", x.itemDescription),
                new XElement("ItemClass", x.itemClass)
            })).ToArray());

        }
    }
    public class Item
    {
        public string itemNumber { get; set; }
        public string itemDescription { get; set; }
        public string itemClass { get; set; }
    }
}

Answer 2

You can manually create additional elements with XmlWriter. But only before or after DataSet/DataTable.

var dt = new DataTable("Item");
dt.Columns.Add("ItemNumber");
dt.Columns.Add("ItemDescription");
dt.Columns.Add("ItemClass");

dt.Rows.Add("number1", "desc1", "class1");
dt.Rows.Add("number2", "desc2", "class2");
dt.Rows.Add("number3", "desc3", "class3");

var ds = new DataSet("Batch");
ds.Tables.Add(dt);



var settings = new XmlWriterSettings { Indent = true };

using (var writer = XmlWriter.Create("test.xml", settings))
{
    writer.WriteStartElement("GreatPlainsIntegration");

    writer.WriteElementString("TransmissionDate", "2015-5-6");
    writer.WriteElementString("TransmissionTime", "20:00");

    //writer.WriteElementString("TargetCatalog", "LWI");
    // and so on

    ds.WriteXml(writer);
}

You don't need a namespace. Dixi.

Perhaps you should place TargetCatalog, BatchSource and other elements before Batch element, at the same level as Transmission* elements? In this case, read the data from XML back into the DataSet will be easy.

The use of DataSet allow set an arbitrary name to DataTable instead of DocumentElement.