Compare/combine attributes in 2 xml files

Hello Experts,

I have 2 xml files as below:

test1.xml:
 <Values>
      <Value>
        <Field name="ID1" value="1000" />
        <Field name="ID2" value="300000" />
        <Field name="Description" value="Description 1" />
        <Field name="CurrentAmount" value="374564.32" />
      </Value>
 <Value>
        <Field name="ID1" value="1000" />
        <Field name="ID2" value="304000" />
        <Field name="Description" value="Description 2" />
        <Field name="CurrentAmount" value="404.52" />
  </Value>
</Values>
 
test2.xml:
<Values>
      <Value>
        <Field name="ID1" value="1000" />
        <Field name="ID2" value="300000" />
        <Field name="PastAmount" value="79727.60" />
      </Value>
      <Value>
        <Field name="ID1" value="1000" />
        <Field name="ID2" value="300010" />
        <Field name="PastAmount" value="44152.11" />
      </Value>
      <Value>
        <Field name="ID1" value="1000" />
        <Field name="ID2" value="304000" />
        <Field name="PastAmount" value="79323.08" />
      </Value>
</Values>

Using LINQ, is there any way to get the difference between CurrentAmount and PastAmount based on the match of the values of ID1 and ID2?
For example,
If ID1 = 1000 and ID2 = 300000, I want to get the difference of 374564.32 - 79727.60.

Thank you very much in advance.
-Chu
LVL 17
ZhaolaiAsked:
Who is Participating?
 
jasonduanConnect With a Mentor Commented:
try this:
XDocument doc1 = XDocument.Load("test1.xml");
XDocument doc2 = XDocument.Load("test2.xml");

var list1 = from x in doc1.Descendants("Value")
	select new
	   {
	   ID1 = (from y in x.Descendants("Field") 
		  where y.Attribute("name").Value == "ID1" 
		  select Convert.ToInt32(y.Attribute("value").Value)).FirstOrDefault(),
	   ID2 = (from y in x.Descendants("Field") 
		  where y.Attribute("name").Value == "ID2" 
		  select Convert.ToInt32(y.Attribute("value").Value)).FirstOrDefault(),
	   CurrentAmount = (from y in x.Descendants("Field") 
		 where y.Attribute("name").Value == "CurrentAmount" 
		 select Convert.ToDouble(y.Attribute("value").Value)).FirstOrDefault()
	   };
var list2 = from x in doc2.Descendants("Value")
	select new
	{
	ID1 = (from y in x.Descendants("Field") 
	   where y.Attribute("name").Value == "ID1" 
	   select Convert.ToInt32(y.Attribute("value").Value)).FirstOrDefault(),
	ID2 = (from y in x.Descendants("Field") 
	   where y.Attribute("name").Value == "ID2" 
	   select Convert.ToInt32(y.Attribute("value").Value)).FirstOrDefault(),
	PastAmount = (from y in x.Descendants("Field") 
		  where y.Attribute("name").Value == "PastAmount" 
		  select Convert.ToDouble(y.Attribute("value").Value)).FirstOrDefault()
	};

var diffList = from x in list1
	   join y in list2 on x.ID1 + "_" + x.ID2 equals y.ID1 + "_" + y.ID2
	   select new { ID1 = x.ID1, ID2 = x.ID2, DiffAmount = x.CurrentAmount - y.PastAmount };

foreach (var item in diffList)
{
	System.Diagnostics.Debug.WriteLine(item.ID1 + " " + item.ID2 + " " + item.DiffAmount);
}

Open in new window

0
 
ZhaolaiAuthor Commented:
WOW. Amazed. Great answer! Thanks a bunch.
0
 
kumar754Commented:
Compressed Version:


XDocument xdoc1 = XDocument.Load("test1.xml");
XDocument xdoc2 = XDocument.Load("test2.xml");

var list1 = from test1 in xdoc1.Descendants("Value")
          join test2 in xdoc2.Descendants("Value")
            on {ID1=test1.Attribute("ID1").Value, ID2=test1.Attribute("ID2").Value}
            equals {ID1=test2.Attribute("ID1").Value, ID2=test2.Attribute("ID2").Value}
          select new {
            ID1 = test1.Attribute("ID1").Value,
            ID2 = test1.Attribute("ID2").Value,
            DiffAmount = Convert.ToDouble(test1.CurrentAmount) - Convert.ToDouble(test2.PastAmount)
          };


0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ZhaolaiAuthor Commented:
@kumar754, thank you for your post.

But when I put your code into test, it does not work, simply because "ID1" and "ID2" are values of attributes, not attribute names, meaning that test1.Attribute("ID1").Value will generate notorious error: Object reference not set to an instance of an object.

Also test1.CurrentAmount and test2.PastAmount are not defined.
Also you need to have "new" on both sides of the "equals".
0
 
kumar754Commented:
@Author:

yeah i didn't tested the code. As I expect these are some of spell checks you already know based on the level of expertize and the code you are trying to achieve. I gave out a solution/concept that you can implement. There is always an alternate way to solve a problem and you figure out which one works out best for you.
0
 
South ModModeratorCommented:
All,
 
Following an 'Objection' by kumar754 (at http://www.experts-exchange.com/Q_26865359.html) to the intended closure of this question, it has been reviewed by at least one Moderator and is being closed as recommended by the Expert.
 
At this point I am going to re-start the auto-close procedure.
 
Thank you,
 
SouthMod
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.