Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

LINQ query to bind the XML data into pivot datagrid

Posted on 2011-05-09
14
Medium Priority
?
498 Views
Last Modified: 2013-12-17
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>

Open in new window


   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
 
                                                       
              Corporations                                     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
0
Comment
Question by:ram27
10 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 35728748
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?
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 35730339
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)

Open in new window

0
 

Author Comment

by:ram27
ID: 35731020
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,
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:ram27
ID: 35733250

  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,

0
 

Author Comment

by:ram27
ID: 35734250
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

0
 

Author Comment

by:ram27
ID: 35741024
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...
 
0
 

Author Comment

by:ram27
ID: 35741042

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
0
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 35799727
Hi ram27;

Here is some sample code for your original question. See if this meets your needs.

var getStats = from s in xdoc.Root.Elements( ns + "PublicCountStatistic" )
               orderby s.Element( ns + "StatisticCode" ).Element( ns + "EnumName" ).Value descending
               group s by s.Element( ns + "StatisticCode" ).Element( ns + "EnumName" ).Value.Substring( 0, 5 )  into sGroup
               select new
               {
                    TYPE = (sGroup.Key == "FOREI") ? "Foreign" : "Domestic", 
                    CORPORATIONS = sGroup.AsEnumerable().Where(s => s.Element( ns + "StatisticCode" ).Element( ns + "EnumName" ).Value.Contains("CORPORATIONS")).Sum (s => int.Parse(s.Element( ns + "StatisticCount" ).Value) ),
                    REVOKED = sGroup.AsEnumerable().Where(s => s.Element( ns + "StatisticCode" ).Element( ns + "EnumName" ).Value.Contains("REVOKEDCORPORATIONS")).Sum (s => int.Parse(s.Element( ns + "StatisticCount" ).Value) ),
                    DELINQUENT = sGroup.AsEnumerable().Where(s => s.Element( ns + "StatisticCode" ).Element( ns + "EnumName" ).Value.Contains("DELINQUENTCORPS")).Sum (s => int.Parse(s.Element( ns + "StatisticCount" ).Value) )
               };

Open in new window


The results of this code is shown in the image below.

Fernando
ScreenShot001.png
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 35902936
Hi ram27; Has my solution worked out for you?
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 35939629
Checking the status of the question. If it has been resolved please close the question.

Thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question