Search code examples
c#xmlxmlwriter

Display the datatable in xml c#

I'm trying to create a xml file with the data in my dataTable. Ultimately, this is what I want to create

<?xml version="1.0" encoding="utf-8"?>
<regisApts xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <JobList>
    <Job id="1245">
      <Date>2010-07-25</Date>
      <Reason>No Access 1</Reason>
      <Comment>Tenant is on holiday</Comment>
      <ExternalJobNumber>123456</ExternalJobNumber>
    </Job>
    <Job id="">
      <Date>2010-07-26</Date>
      <Reason>No Access 2</Reason>
      <Comment>Tenant out at work</Comment>
      <ExternalJobNumber>123456</ExternalJobNumber>
    </Job>
    <Job id="1453">
      <Date>2010-07-25</Date>
      <Reason>No Access 1</Reason>
      <Comment>Tenant in hospital</Comment>
    </Job>
  </JobList>
</regisApts>

I managed to generate a xml with foreach, but it's overiding the xml when inserting the next row. Any help is really appreciated. Thanks

foreach (DataRow row in dt.Rows)
{
    xmlCostCode = row["CostCode"].ToString();
    xmlReason = row["Reason"].ToString();
    xmlComment = row["PropertyCode"].ToString();
    xmlFilePath = xmlFolderPath + "test" + ".xml";

    xmlContent = "<JobList><Job Id=\"" + xmlCostCode + "\"><Date>2017-07-18</Date><Reason>" + xmlReason + "</Reason><Comment>" + xmlComment + "</Comment></Job></JobList>";

    XmlDocument xdoc = new XmlDocument();
    xdoc.LoadXml(xmlContent);
    xdoc.Save(xmlFilePath);
}

Results I get

<JobList>
    <Job id="1245">
      <Date>2010-07-25</Date>
      <Reason>No Access 1</Reason>
      <Comment>Tenant is on holiday</Comment>
      <ExternalJobNumber>123456</ExternalJobNumber>
    </Job>
<JobList>
Solution

  • Every time you do 

     XmlDocument xdoc = new XmlDocument();
 xdoc.LoadXml(xmlContent);
 xdoc.Save(xmlFilePath);

    It writes over your previous document with the most recent row, so try 

    xmlContent = "<regisApts xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"><JobList>";
foreach (DataRow row in dt.Rows)
{
   xmlCostCode = row["CostCode"].ToString();
   xmlReason = row["Reason"].ToString();
   xmlComment = row["PropertyCode"].ToString();
   xmlFilePath = xmlFolderPath + "test" + ".xml";

   xmlContent = xmlContent +"<Job Id=\"" + xmlCostCode + "\"><Date>2017-07-18</Date><Reason>" + xmlReason + "</Reason><Comment>" + xmlComment + "</Comment></Job>";

}
xmlContent = xmlContent +"</JobList></regisApts>";
XmlDocument xdoc = new XmlDocument();
xdoc.LoadXml(xmlContent);
xdoc.Save(xmlFilePath);

    This way you are building the string with each row and once that is done you create the XML doc.