how to sum the values from an XML file based on certain criteria

Question

i need to sum up the values from an XML file based on some criteria
my XMl Is as follows

<?xml version="1.0" encoding="utf-8"?>
<transactions>
    <transcation ID="1">
        <BankName>ABC</BankName>
        <TemplatModel>CT1</TemplatModel>
        <Date>12/12/2014</Date>
        <Payeename>xyz</Payeename>
        <Amount>200</Amount>
    </transcation>
    <transcation ID="2">
        <BankName>EFG</BankName>
        <TemplatModel>CT2</TemplatModel>
        <Date>12/12/2014</Date>
        <Payeename>xyz</Payeename>
        <Amount>100</Amount>
    </transcation>
    <transcation ID="3">
        <BankName>ABC</BankName>
        <TemplatModel>CT1</TemplatModel>
        <Date>10/12/2014</Date>
        <Payeename>pqr</Payeename>
        <Amount>400</Amount>
    </transcation>
    <transcation ID="4">
        <BankName>EFG</BankName>
        <TemplatModel>CT2</TemplatModel>
        <Date>11/12/2014</Date>
        <Payeename>asd</Payeename>
        <Amount>300</Amount>
    </transcation>
</transactions>

I need to find the sum of amount where BankName="ABC" and in another query i need to find the sum of amount whose date less than "12/12/2014"

and one more doubt which xml structure is better

<transactions>
    <transcation ID="1" BankName="ABC" TemplatModel="CT1" Date="12/12/2014" Payeename="asd" Amount="200"/>
    <transcation ID="2" BankName="EFG" TemplatModel="CT2" Date="12/12/2014" Payeename="xyz" Amount="100"/>
    <transcation ID="3" BankName="ABC" TemplatModel="CT1" Date="10/12/2014" Payeename="pqr" Amount="400"/>
    <transcation ID="4" BankName="EFG" TemplatModel="CT2" Date="11/12/2014" Payeename="asd" Amount="300"/>
</transactions>

Edited Question

i am getting an error when returning the query result to List

 public static List<string> banksearch(string bankname, string sdate = null, string edate = null, string condition = null)
    { 
    }

return transactions
                        .Where(t => t.BankName == bankname && t.Date == startdate)
                         .Select(t => new
                                 {
                                     TransactionID = t.TransactionID,
                                     BankName = t.BankName,
                                     TemplateModel = t.TemplateModel,
                                     Date = t.Date,
                                     PayeeName = t.PayeeName,
                                     Amount = t.Amount
                                 }).ToList();

my error is

Error   2   Cannot implicitly convert type 'System.Collections.Generic.List<AnonymousType#1>' to 'System.Collections.Generic.List<string>'

Answer 1

I'm going to offer a slightly different approach. In your question you mention two queries, and I could see you needing other queries. In that case, I'd recommend parsing the XML into an object and having a collection of those objects which you could then query against.

First, the object (class) to hold the data for a transaction:

public class Transaction
{

    public int TransactionID { get; set; }
    public string BankName { get; set; }
    public string TemplatModel {get; set; }
    public DateTime Date { get; set; }
    public string PayeeName { get; set; }
    public decimal Amount { get; set; }
}

Note that I chose decimal to represent amount - this is a normal choice for monetary units.

Next, you can use LINQ to XML to parse the XML into a collection of Transaction objects, like this (based on your first XML), and assuming you have an XDocument named xDoc representing the XML file:

List<Transaction> transactions = xDoc.Root.Elements("transcation")
         .Select(x => new Transaction() {
                      TransactionID = Convert.ToInt32((string)x.Attribute("ID")),
                      BankName = (string)x.Element("BankName"),
                      TemplatModel = (string)x.Element("TemplatModel"),
                      Date = Convert.ToDateTime((string)x.Element("Date")),
                      PayeeName = (string)x.Element("Payeename"),
                      Amount = Convert.ToDecimal((string)x.Element("Amount"))
         }).ToList();

Note the use of (string) - this will gracefully handle missing elements without throwing a null reference exception.

Finally, with your transactions collection you can run any type of query you want, including the two you mentioned in your question:

var banksByName = transactions.Where(t => t.BankName == "ABC")

And

DateTime selectedDate = new DateTime(2014, 12, 22);
var transByDate = transactions.Where(t => t.Date < selectedDate);

The advantage of this approach is that you query the XML one time to develop a collection of objects, and then you can query that collection as often as you need.

Edited to add fix for new error

The reason you're getting the Cannot implicitly convert type 'System.Collections.Generic.List<AnonymousType#1>' to 'System.Collections.Generic.List<string>' error is that you're method is expecting to return List<string>, and you're actually returning a List<AnonymousType>. When you use the new keyword in LINQ Select statement without a concrete class, you're creating an anonymous type, which can't be returned in a method.

To fix this, change the return type of your method to List<Transaction> and add Transaction after the new keyword:

public static List<Transaction> banksearch(string bankname, string sdate = null, string edate = null, string condition = null)
{ 

    return transactions.Where(t => t.BankName == bankname && t.Date == startdate)
                       .Select(t => new Transaction() {
                                 TransactionID = t.TransactionID,
                                 BankName = t.BankName,
                                 TemplateModel = t.TemplateModel,
                                 Date = t.Date,
                                 PayeeName = t.PayeeName,
                                 Amount = t.Amount
                        }).ToList();

Answer 2

You can use LINQ to XML for this:

DateTime exclusiveDateTime = new DateTime(2014, 12, 12);

// instantiate and load your XDocument as yourXDocument here

XElement transcationsElement = yourXDocument.Root;

IEnumerable<int> amounts = from transcation in transcationsElement.Descendants("transcation")
                           where transcation.Attribute("BankName").Value == "ABC" &&
                               Convert.ToDateTime(transcation.Attribute("Date").Value) < exclusiveDateTime
                           select Convert.ToDecimal(transcation.Attribute("amount"));

decimal amountsSum = amounts.Sum();

I'm using the second XML form you've posted because I find it easier to deal with related attributes of an element as opposed to children of the element, but that's mostly personal preference on my part. This solution assumes that every one of your Date and Amount attributes are properly formed as DateTime strings and ints, respectively. You'll encounter InvalidCastExceptions if that's not the case.

Note - if you're thinking about extending your XML, and you'll need to perform more queries on your transcation elements, you should probably just create a class to encapsulate the data contained in each element, and use LINQ to Objects to parse your data and instantiate each object.

Answer 3

I will help you with one query (sum of amount) and another query you try yourself based on the first query

int sum = 0;
transactionXml.Descendants("transaction").ToList().ForEach(i => {
    if (String.Equals(i.Element("BankName").Value, "abc", StringComparison.OrdinalIgnoreCase))
        sum += Convert.ToInt32(i.Element("Amount").Value);
});

Regarding XML structure, Bank name and Payee name can have special characters which can break your xml. Hence suggested XML structure is as follows

<transactions>
    <transcation ID="1" TemplatModel="CT1" Date="12/12/2014" Amount="200">
        <BankName>ABC</BankName>
        <Payeename>asd</Payeename>
    </transcation>
    ***
    ***
    ***
</transactions>