LINQ query to bind the XML data into pivot datagrid

This is the perfect teaching opportunity, since you say that you are new to LINQ.  How much do you already know about LINQ-to-XML?  Have you attempt anything yet?  If so, can you show me what you have?

Given the data loaded into an XElement variable, xml
- e.g.
    Dim xml as XElement = <Root xmlns="Acc.StarPlus.XsdLibrary">
                  <PublicCountStatistic>
                      <StatisticCode xmlns="Acc.StarPlus.XsdLibrary">
...
...
                  </Root>

The attached two linq statements filter by Type CT and then create an enumerable of with boolean fields indicating the source of each line.

It should be fairly easy then to summarise the data using a further query on ctParsed

Dim CT = From x As XElement In xml.Elements(aw + "PublicCountStatistic")
                 Where x.Element(aw + "StatisticType") = "CT"
                 Select DisplayCode = x.Element(aw + "StatisticCode").Element(aw + "DisplayCode").Value
        Dim ctParsed = From c In CT
                      Select Foreign = "146".Contains(c),
                      Revoked = "12".Contains(c), Delinqent = "45".Contains(c), Corporation = "67".Contains(c)

Select allOpen in new window

Hi All,

Thanks for your replies,

I tried writing the below.. I am taking the XML data into a collection

private void BindDataGrid(ObservableCollection<GEN.PublicCountStatistic> StatisticCount)
        {
            var pcs = from p in StatisticCount
                      where p.StatisticType.DisplayCode == "CT"
                      group p by new { p.StatisticCode.EnumName }
                          into pcsdata
                          select new
                          {

                              BusinessCorps = pcsdata.Where(p => p.StatisticCode.EnumName == "DomesticBusinessCorporations").FirstOrDefault().StatisticCount,
                              NonProfitCorps = pcsdata.Where(p => p.StatisticCode.EnumName == "DomesticNonProfitCorporations").FirstOrDefault().StatisticCount,
                              ProfessionalCorps = pcsdata.Where(p => p.StatisticCode.EnumName == "DomesticProfessionalCorporations").FirstOrDefault().StatisticCount,
                              OtherCorps = pcsdata.Where(p => p.StatisticCode.EnumName == "Domestic Other Corporations").FirstOrDefault().StatisticCount

                          };

            dgCurrentStatisticsPart1.ItemsSource = pcs;
        }

 But I am getting some NULl Exception..  
 Please advice me how to write to bind the data into a PIVOT Grid
And one more thing is if it is a ForeignCorporation it shold display under it(beside the Row header Foreign) and if it is Domestic then beside the Row header Domestic

Thanks,

  Please help me to solve this...

    HI,

 I have an XML which is is like below

 <Root xmlns="Acc.StarPlus.XsdLibrary">
 <PublicCountStatistic>
  <StatisticCode xmlns="Acc.StarPlus.XsdLibrary">
   <CategoryId>70</CategoryId>
   <CategoryValueId>2101</CategoryValueId>
   <DisplayCode>1</DisplayCode>
   <DisplayName>FOREIGN REVOKED CORPS </DisplayName>
   <EnumName>FOREIGNREVOKEDCORPS</EnumName>
    <IsActive>1</IsActive>
   <SortOrder>1</SortOrder>
  </StatisticCode>
  <StatisticCount>10480</StatisticCount>
  <StatisticDate>2009-12-31T00:00:00</StatisticDate>
  <StatisticType>CT</StatisticType>
 </PublicCountStatistic>

 <PublicCountStatistic>
  <StatisticCode xmlns="Acc.StarPlus.XsdLibrary">
   <CategoryId>70</CategoryId>
   <CategoryValueId>2102</CategoryValueId>
   <DisplayCode>2</DisplayCode>
   <DisplayName>DOMESTIC REVOKED CORPORATIONSDisplayName>
   <EnumName>DOMESTICREVOKEDCORPORATIONS<EnumName>
   <IsActive>1</IsActive>
   <SortOrder>1</SortOrder>
  </StatisticCode>
  <StatisticCount>6</StatisticCount>
  <StatisticDate>2009-12-31T00:00:00</StatisticDate>
  <StatisticType>CT</StatisticType>
 </PublicCountStatistic>

 <PublicCountStatistic>
  <StatisticCode xmlns="Acc.StarPlus.XsdLibrary">
   <CategoryId>70</CategoryId>
   <CategoryValueId>2104</CategoryValueId>
   <DisplayCode>4</DisplayCode>
   <DisplayName>FOREIGN DELINQUENT CORPS<DisplayName>
   <EnumName>FOREIGNDELINQUENTCORPS</EnumName>
   <IsActive>1</IsActive>
   <SortOrder>1</SortOrder>
  </StatisticCode>
  <StatisticCount>934</StatisticCount>
  <StatisticDate>2009-12-31T00:00:00</StatisticDate>
 <StatisticType>CT</StatisticType>
 </PublicCountStatistic>

 <PublicCountStatistic>
  <StatisticCode xmlns="Acc.StarPlus.XsdLibrary">
   <CategoryId>70</CategoryId>
   <CategoryValueId>2105</CategoryValueId>
   <DisplayCode>5</DisplayCode>
   <DisplayName>DOMESTIC DELINQUENT CORPS </DisplayName>
   <EnumName>DOMESTICDELINQUENTCORPS</EnumName>
   <IsActive>1</IsActive>
   <SortOrder>1</SortOrder>
  </StatisticCode>
  <StatisticCount>4540</StatisticCount>
  <StatisticDate>2009-12-31T00:00:00</StatisticDate>
 <StatisticType>CT</StatisticType>
 </PublicCountStatistic>

 <PublicCountStatistic>
  <StatisticCode xmlns="Acc.StarPlus.XsdLibrary">
   <CategoryId>70</CategoryId>
   <CategoryValueId>2106</CategoryValueId>
   <DisplayCode>6</DisplayCode>
   <DisplayName>FOREIGN CORPORATIONS</DisplayName>
   <EnumName>FOREIGNCORPORATIONS</EnumName>
   <IsActive>1</IsActive>
   <SortOrder>1</SortOrder>
  </StatisticCode>
  <StatisticCount>58897</StatisticCount>
  <StatisticDate>2009-12-31T00:00:00</StatisticDate>
 <StatisticType>CT</StatisticType>
 </PublicCountStatistic>

 <PublicCountStatistic>
  <StatisticCode xmlns="Acc.StarPlus.XsdLibrary">
   <CategoryId>70</CategoryId>
   <CategoryValueId>2107</CategoryValueId>
   <DisplayCode>7</DisplayCode>
   <DisplayName>DOMESTIC CORPORATIONS</DisplayName>
   <EnumName>DOMESTICCORPORATIONS</EnumName>
   <IsActive>1</IsActive>
   <SortOrder>1</SortOrder>
  </StatisticCode>
  <StatisticCount>550861</StatisticCount>
  <StatisticDate>2009-12-31T00:00:00</StatisticDate>
  <StatisticType>CT</StatisticType>
 </PublicCountStatistic>

 <PublicCountStatistic>
  <StatisticCode xmlns="Acc.StarPlus.XsdLibrary">
   <CategoryId>70</CategoryId>
   <CategoryValueId>2108</CategoryValueId>
   <DisplayCode>8</DisplayCode>
   <DisplayName>FOREIGN CORPORATIONS ADDED</DisplayName>
   <EnumName>FOREIGNCORPORATIONSADDED</EnumName>
   <IsActive>1</IsActive>
   <SortOrder>1</SortOrder>
  </StatisticCode>
  <StatisticCount>202</StatisticCount>
  <StatisticDate>2009-12-31T00:00:00</StatisticDate>
<StatisticType>MTH</StatisticType>
 </PublicCountStatistic>
 
 <PublicCountStatistic>
  <StatisticCode xmlns="Acc.StarPlus.XsdLibrary">
   <CategoryId>70</CategoryId>
   <CategoryValueId>2110</CategoryValueId>
   <DisplayCode>10</DisplayCode>
   <DisplayName>DOMESTIC CORPORATIONS REINSTATED</DisplayName>
   <EnumName>DOMESTICCORPORATIONSREINSTATED</EnumName>
   <IsActive>1</IsActive>
   <SortOrder>1</SortOrder>
  </StatisticCode>
  <StatisticCount>225</StatisticCount>
  <StatisticDate>2009-12-31T00:00:00</StatisticDate>
 <StatisticType>MTH</StatisticType>
 </PublicCountStatistic>

   basically what i need to do here is :  I need to write a LINQ query
   To bind the above XML data to the datagrids in silverlight
   For Statistic type CT,  i need to bind the grid like below
 
                                                       
               BusinessCorporations                                   Revoked Corps        Delinquent Corps
             
Foreign       58897((i,e) ForeignCorporation                    10480                  934
                    Statistic code)



Domestic     550861                                              6                     4540

 
  Please i am new to LINQ, please help me to write a LINQ query to bind the above XML data to the datagrid like above

   Thanks for your replies,

 I am taking the XML data into a collection

 The Row headers Foreign and Domestic are not there in the XML data
  Ex: If from the XML StatisticCode.EnumName="ForeignBusinessCorporations" then it should display beside the  Row header Foreign and if it is DomesticBusinessCorporation, it should display beside the rowheader Domestic

     Thanks,

i have a data in a collection object  taken from XML.

 I need to format the data in such a way that...

 from  my xml.  for  PublicCountStatistics.EnumName is ForeignCorporations, i need to display the StatisticCount of it

and for the DomesticCorporations, i need to display its StatisticCount  like below

In the XML it will be written as DOMEsticCorporations and ForeignCorporations, but i will have a header in my grid called

Corporations which need to bind like below ... I even don't know how can i able to display the Foreign and Domestic as a Row header also.

Below is thr GRid

                                  Corporations  

   FOReign              202

  DOMESTIC           205

 the below is the XML format.....

 <PublicCountStatistic>
  <StatisticCode xmlns="Acc.StarPlus.XsdLibrary">
   <CategoryId>70</CategoryId>
   <CategoryValueId>2108</CategoryValueId>
   <DisplayCode>8</DisplayCode>
   <DisplayName>FOREIGN CORPORATIONS </DisplayName>
   <EnumName>FOREIGNCORPORATIONS</EnumName>
   <IsActive>1</IsActive>
   <SortOrder>1</SortOrder>
  </StatisticCode>
  <StatisticCount>202</StatisticCount>
  <StatisticDate>2009-12-31T00:00:00</StatisticDate>
<StatisticType>MTH</StatisticType>
</PublicCountStatistic>

 

<PublicCountStatistic>
  <StatisticCode xmlns="Acc.StarPlus.XsdLibrary">
   <CategoryId>70</CategoryId>
   <CategoryValueId>2108</CategoryValueId>
   <DisplayCode>8</DisplayCode>
   <DisplayName>DOMESTIC CORPORATIONS </DisplayName>
   <EnumName>DOMESTICCORPORATIONS</EnumName>
   <IsActive>1</IsActive>
   <SortOrder>1</SortOrder>
  </StatisticCode>
  <StatisticCount>205</StatisticCount>
  <StatisticDate>2009-12-31T00:00:00</StatisticDate>
<StatisticType>MTH</StatisticType>
</PublicCountStatistic>

I hope you understood my issue, please help me with some code samples

Thanks

My stored procedure return an XML llike below

 <PublicCountStatistic>
  <StatisticCode xmlns="Acc.StarPlus.XsdLibrary">
   <CategoryId>70</CategoryId>
   <CategoryValueId>2108</CategoryValueId>
   <DisplayCode>8</DisplayCode>
   <DisplayName>FOREIGN CORPORATIONS </DisplayName>
   <EnumName>FOREIGNCORPORATIONS</EnumName>   <IsActive>1</IsActive>
   <SortOrder>1</SortOrder>
  </StatisticCode>
 <StatisticCount>202</StatisticCount>  
<StatisticDate>2009-12-31T00:00:00</StatisticDate>
<StatisticType>CUR</StatisticType>
</PublicCountStatistic>

 

<PublicCountStatistic>
  <StatisticCode xmlns="Acc.StarPlus.XsdLibrary">
   <CategoryId>70</CategoryId>
   <CategoryValueId>2108</CategoryValueId>
   <DisplayCode>8</DisplayCode>
   <DisplayName>DOMESTIC CORPORATIONS </DisplayName>
  <EnumName>DOMESTICCORPORATIONS</EnumName>
 <IsActive>1</IsActive>
   <SortOrder>1</SortOrder>
  </StatisticCode>
 <StatisticCount>205</StatisticCount>
<StatisticDate>2009-12-31T00:00:00</StatisticDate>
<StatisticType>MTH</StatisticType>
</PublicCountStatistic>

I wrote a method like below taking XML data in a collection

i have a class with set of properties like
 public class PublicCountStatistic
    {
        public string RowHeader1
        {
            get;
            set;
        }
        public int BusinessCorps
        {
            get;
            set;
        }

  var pcs = from p in StatisticCount
                      where p.StatisticType.DisplayCode == "CUR"
                      group p by new { p.StatisticType.DisplayCode}
                          into pcsdata
                          select new PublicCountStatistic()
                           {  
                               //EnumName = pcsdata.Key,
                               RowHeader1 = "Domestic"
                            BusinessCorps = pcsdata.Where(p => p.StatisticCode.EnumName == "DomesticBusinessCorporations").SingleOrDefault().StatisticCount,
                       };                        

            List<PublicCountStatistic> publicCountStatistics = pcs.ToList();
            dgCurrentStatisticsPart1.ItemsSource = publicCountStatistics;

        My silverlight Grid have  the bindings like below
         
       <data:DataGrid.Columns>
                        <data:DataGridTextColumn Header="" Binding="{Binding Path=RowHeader1}" />
                                <data:DataGridTextColumn Header="Business Corps" Binding="{Binding Path=BusinessCorps}"/>
</data:DataGrid.Columns>

and my output is like beliow
                 
                                         BusinessCoporations
 
     Domestic                            2108


But i need to get the output as Grid as
                         
                                                       Business Corps

                Domestic                                   2108

                 Foreign                                      205


How can i bind the Foreign corporations statisticcount also to the grid
what i code i need to add to the above, please help me on this...
 

Sorry.... i wrote the value wrong

and my output is like beliow
                 
                                         BusinessCoporations
 
     Domestic                           205

But i need to get the output as Grid as
                         
                                                       Business Corps

                Domestic                                   205

                 Foreign                                      202

Hi ram27; Has my solution worked out for you?

Checking the status of the question. If it has been resolved please close the question.

Thank you.