Naveen's Weblog

Bridge to future

Grouping XML Elements using Group By

Posted by codingsense on September 24, 2008


Since you have seen how to create an XML and fetch simple data from it, lets move to next level. In this sample we shall Group the elements and create our own XML from an existing XML.

Download Sample – 26Kb

Lets us consider a clientInfo XML which will store client Name and his Address(City,Area), now consider a situaltion where we would require to list all the employes grouped with their area name, How it can be done?. Here it goes.

XElement GroupAreas = new XElement("Areas",
            from client in ClientInfo.Elements()
            group client by client.Element("Address").Element("Area").Value into GroupedNames
            select new XElement(GroupedNames.Key,
                   GroupedNames.Elements("Name")
            ));

After this the clients will be grouped on their area name.

OutPut:

Happy Learning 🙂

Advertisements

4 Responses to “Grouping XML Elements using Group By”

  1. zappingz said

    Hi,

    Do you know how this query can be converted to linq

    select * from employee emp where dateofjoin in (
    select max(dateofjoin)
    from employee
    group by deptid, managerid)
    and empid.id=empidid
    order by empid

    Thanks

    • codingsense said

      Hi,

      First let us understand your requirement,
      In Employee class you have the following fields ID, DateOfJoin, DeptID, ManagerID.
      You want the list of employees who has joined recently in each dept under each manager and lastly the list of each employee should be ordered by their ID.

      Here is the query for the above requirement.

      var RecentlyJoinedEmployees =
      from Emp in ListOfEmp
      from RecentEmp in
      (from recentEmp in ListOfEmp
      group recentEmp by new { recentEmp.DeptID, recentEmp.ManagerID } into GroupedEmployees
      select new
      {
      MaxDate = GroupedEmployees.Max(e => e.DateOfJoin),
      DeptId = GroupedEmployees.Key.DeptID,
      ManagerID = GroupedEmployees.Key.ManagerID
      })
      where Emp.DeptID == RecentEmp.DeptId &&
      Emp.ManagerID == RecentEmp.ManagerID &&
      Emp.DateOfJoin == RecentEmp.MaxDate
      orderby Emp.ID
      select Emp;

      Where ListOfEmp list a List.
      Employee is a class with properties ID, DateOfJoin ,DeptID , ManagerID.

      If any doubts please feel free to ask. 🙂

      Thanks,
      Naveen Prabhu

  2. zappingz said

    thx for the help Naveen. still am only getting the feel of it. one more pls cannot get to join on 2 tables by 2 columns.

    select item.*, dept.* from item, dept
    where item.deptid=dept and item.catgid=dept.catgid and item.isactive=1 and dept.isactive=1 and item.quantity>0

    something like this??

    from item in items
    join dept in depts
    on item.deptid equals dept.deptid && item.catgid=dept.catgid
    where item.isactive=1 && dept.isactive=1 && item.quantity>0
    select item, dept

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: