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

Can you share what you have tried so far? Are you required to stream your output directly without creating a temporary, in-memory representation of the XML, or could you write your `DataTable` to an `XElement` then modify it and stream it out? — dbc, Jun 22 '16 at 20:49
I am not restricted. I am building this from scratch. You'll have to forgive me in that I have not created XML before. I have been watching some Pluralsight videos and trying to learn. So in theory I know how to create an element and then create child elements to it. What I am failing to build a mental concept of is how I can create elements from the datatable which are children to another element I have already created in memory. — CodenameCain, Jun 22 '16 at 21:18

score 1 · Answer 1 · answered Jun 22 '16 at 22:29

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.

Thank you for the use of a DataSet. I was sharing my code attempt while your answer came through and I was close. Are you able to comment on the xmlns:integration portion of the GreatPlainsIntegration element? I do not know what that is or how to create. — CodenameCain, Jun 22 '16 at 22:43
Actually I still have a problem. I need to have elements at the same level as each element and have them all be children of the element. Having the element exist by naming the DataSet is not allowing this. — CodenameCain, Jun 22 '16 at 22:57
@CodenameCain - to skip the `` element, you could use `ElementSkippingXmlTextWriter ` from [Custom xmlWriter to skip a certain element?](https://stackoverflow.com/questions/32149676/custom-xmlwriter-to-skip-a-certain-element/32150990#32150990). There's an earlier version [here](https://stackoverflow.com/questions/27360018/remove-diffgram-and-newdataset-tag-from-xml-in-c-sharp) that shows how to skip the root element of a data set. — dbc, Jun 23 '16 at 03:03

score 1 · Accepted Answer · answered Jun 23 '16 at 03:22

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

Create XML with DataTable.WriteXML but with additional Elements?

2 Answers2