CODEWITHSUNDEEP
c#xmllinq
Silentbob
Silentbob

Reputation: 3065

Parse XML to a datatable

Having a lot of trouble with this :(

Based on my previous question here I can now get all of the prices from my XML file but what if I wanted to get the startdate (which will change throughout the document) and the price (which will change throughout the document) and put it in a data table. As you can see from the previous question I was trying to put it into a datatable but if I integrate the solution provided I get the same FormalName and price for the number of lines in the XML file (approx 700). Here is the code I am working on. 

string[] arr = new string[3];
var dr = from n in prices.Descendants("COLUMN")
         select new string[]
         {
             arr[0] = n.Attribute("FormalName").Value
         };

foreach (var item in dr)
{
    dt.Rows.Add(item[0]);
}

Here is the solution provided in the previous question which I need to integrate into my solution.

var result = from c in prices.Descendants("COLUMN")
             where c.Attribute("FormalName")?.Value == "Price"
             select c.Value;

I did think about using a linq statement for each section I wanted to extract so startdate and price and then combining them some how but I couldnt be sure on the order?

UPDATE:

Example XML

<myPrices>
  <Prices Date="10-Oct-18" ReportName="myReport">
    <Record RecordId="1">
      <COLUMN DisplayName="Start Date" FormalName="startdate">10.10.2018</COLUMN>
      <COLUMN DisplayName="End Date" FormalName="Price">10</COLUMN>
    </Record>
    <Record RecordId="2">
      <COLUMN DisplayName="Start Date" FormalName="startdate">11.10.2018</COLUMN>
      <COLUMN DisplayName="End Date" FormalName="Price">20</COLUMN>
    </Record>
  </Prices>
</myPrices>

Data Table

Table example

Upvotes: 0

Views: 350

Answers (2)

gmiley
gmiley

Reputation: 6604

You can do it several ways. Linq is one way, but for a simple example, try the following:

private DataTable LoadDataTable()
{
    DataTable dtable = new DataTable("Prices");
    dtable.Columns.Add("startdate", typeof(string));
    dtable.Columns.Add("price", typeof(string));

    foreach(XmlElement elem in xdoc.GetElementsByTagName("Record")) 
    {
        DataRow row = dtable.NewRow();
        string startdate = elem.SelectSingleNode("COLUMN[@FormalName=\"startdate\"]").InnerText;
        string price = elem.SelectSingleNode("COLUMN[@FormalName=\"Price\"]").InnerText;

        row["startdate"] = startdate;
        row["price"] = price;
        dtable.Rows.Add(row);
    }
    return dtable;
}

You can filter your nodes using an XPath pattern.

Upvotes: 1

Priyank_Vadi
Priyank_Vadi

Reputation: 1138

I would first create a DataTable with the columns that you require, then populate it via Linq-to-XML.

You could use a Select query to create an object that represents each row, then use the standard approach for creating DataRows for each item ...

class Quest
{
    public string Answer1;
    public string Answer2;
    public string Answer3;
    public string Answer4;
}

public static void Main()
{
    var doc = XDocument.Load("filename.xml");

    var rows = doc.Descendants("QuestId").Select(el => new Quest
    {
        Answer1 = el.Element("Answer1").Value,
        Answer2 = el.Element("Answer2").Value,
        Answer3 = el.Element("Answer3").Value,
        Answer4 = el.Element("Answer4").Value,
    });

    // iterate over the rows and add to DataTable ...

}

Upvotes: 0

Related Questions