xml with column and cell names to gridview or table RSS

• • Reply
  

  jcmartin

  Member

  2 Points

  27 Posts

  xml with column and cell names to gridview or table

  Jan 02, 2013 03:54 PM|LINK

  My apologies if the answer to this question appears elsewhere in this forum. I have been searching for days and I am stumped.

   We have a rest request from a client API that we cannot parse. The XML structure is

  - <result>
    <status>success</status>
  - <responseData>
  - <reportData>
  - <columns>
    <column>Date</column>
    <column>clicks</column>
    <column>conversions</column>
    <column>impressions</column>
    <column>Payout</column>
    <column>Average Payout/Action</column>
    <column>EPC</column>
    <column>CTR</column>
    <column>Conv</column>
    <column>ECPM</column>
    </columns>
  - <report>
  - <row>
    <cell>2012-12-24</cell>
    <cell>24</cell>
    <cell>2</cell>
    <cell>802</cell>
    <cell>3.95</cell>
    <cell>1.9750000000000000</cell>
    <cell>0.164583333333333</cell>
    <cell>0.029925187032419</cell>
    <cell>0.0833333333333333</cell>
    <cell>4.92518703241895</cell>
    </row>
  - <row>
    <cell>2012-12-25</cell>
    <cell>23</cell>
    <cell>0</cell>
    <cell>704</cell>
    <cell>0.00</cell>
    <cell>0</cell>
    <cell>0</cell>
    <cell>0.0326704545454545</cell>
    <cell>0</cell>
    <cell>0</cell>
    </row>
    </report>
    </reportData>
    </responseData>
  - <errorMessages>
    <errorMessage />
    </errorMessages>
    </result>

   

  What we want to output to a web form is:

   

  Date	Clicks	Conversions	impressions	Etc
  2012/12/24	24	2	802	Etc
  2012/12/25	23	0	704	Etc

   The <columns> will change based on the http request, so we have to populate the header row dynamically.

   

  Thanks in advance, I feel that there is a simple answer that is eluding us.

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: xml with column and cell names to gridview or table

  Jan 03, 2013 04:19 AM|LINK

  Hello:

  1) You can fetch all the columns name.

  2) and then fetch each value from the <row> and then do calculation:

  Now here's an example:

  public class Program
      {
          static void Main(string[] args)
          {
              XDocument doc = XDocument.Load("XMLFile1.xml");
   
              //Fetch all the columns
              var columns = from item in doc.Descendants("column")
                            select item.Value;
   
              //Create a DataTable
              DataTable dt = new DataTable();
              foreach (var item in columns)
              {
                  dt.Columns.Add(item);
              }
   
              foreach (var item in doc.Descendants("row"))
              {
                  DataRow dr = dt.NewRow();
                  var results = item.Elements().ToArray();
   
                  for (int i = 0; i < results.Length; i++)
                  {
                      dr[i] = results[i];
                  }
                  dt.Rows.Add(dr);
              }
          }
      }

• • Reply
  

  jcmartin

  Member

  2 Points

  27 Posts

  Re: xml with column and cell names to gridview or table

  Jan 03, 2013 01:11 PM|LINK

  Thanks, but I can't make this work. I keep getting all my data in one cell like:

  Date

  clicks

  conversions

  impressions

  Payout

  Average Payout/Action

  EPC

  CTR

  Conv

  ECPM

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: xml with column and cell names to gridview or table

  Jan 04, 2013 12:56 AM|LINK

  jcmartin

  Thanks, but I can't make this work. I keep getting all my data in one cell like:

  sorry please have a try like this:

   public class MainTest
      {
          static void Main(string[] args)
          {
              XDocument doc = XDocument.Load("XMLFile1.xml");
   
              //Fetch all the columns
              var columns = from item in doc.Descendants("column")
                            select item.Value;
   
              //Create a DataTable
              DataTable dt = new DataTable();
              foreach (var item in columns)
              {
                  dt.Columns.Add(item);
              }
   
              foreach (var item in doc.Descendants("row"))
              {
                  DataRow dr = dt.NewRow();
                  var results = item.Elements().ToArray();
   
                  for (int i = 0; i < results.Length; i++)
                  {
                      dr[i] = results[i].Value;
                  }
                  dt.Rows.Add(dr);
              }
   
              //Print out
              foreach (DataRow row in dt.Rows)
              {
                  foreach (DataColumn col in dt.Columns)
                  {
                      Console.Write(row[col].ToString()+"  ");
                  }
                  Console.WriteLine();
              }
          }
      }

  • Edited by jcmartin on Jan 06, 2013 01:00 PM
  • Edited by jcmartin on Jan 06, 2013 01:00 PM
• • Reply
  

  jcmartin

  Member

  2 Points

  27 Posts

  Re: xml with column and cell names to gridview or table

  Jan 04, 2013 04:35 PM|LINK

  Thanks for trying. I guess I'm not skilled enough to make this work. I convert to VB and your code won't convert for me, and I don't use the console. I need to output to a webform via gridview, reapeater, table, anything.

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: xml with column and cell names to gridview or table

  Jan 05, 2013 02:37 AM|LINK

  Hi again,

  Now here's a complete thing out for you, please put your codes inside the aspx page:

  'Fetch all the columns
  Dim columns = From item In doc.Descendants("column")item.Value
  
  'Create a DataTable
  Dim dt As New DataTable()
  For Each item As var In columns
  	dt.Columns.Add(item)
  Next
  
  For Each item As var In doc.Descendants("row")
  	Dim dr As DataRow = dt.NewRow()
  	Dim results = item.Elements().ToArray()
  
  	For i As Integer = 0 To results.Length - 1
  		dr(i) = results(i).Value
  	Next
  	dt.Rows.Add(dr)
  Next
  GridView1.DataSource = dt
  GridView1.DataBind()

  • Edited by decker dong - msft on Jan 05, 2013 02:37 AM
  • Edited by jcmartin on Jan 06, 2013 12:59 PM
  • Edited by decker dong - msft on Jan 11, 2013 10:00 AM
• • Reply
  

  jcmartin

  Member

  2 Points

  27 Posts

  Re: xml with column and cell names to gridview or table

  Jan 05, 2013 12:21 PM|LINK

  Compiler Error Message: BC30205: End of statement expected.
  
  Source Error:
  
  

  Line 18: Line 19: 'Fetch all the columns Line 20: Dim columns = From item In doc.Descendants("column")item.Value  Line 21: Line 22: 'Create a DataTable

  
  
  

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: xml with column and cell names to gridview or table

  Jan 06, 2013 02:25 AM|LINK

  jcmartin

  Dim columns = From item In doc.Descendants("column")item.Value 
  

  Hi,

  You can just use:

  Dim columns = From item In doc.Descendants("column") Select item.Value 

  • Edited by decker dong - msft on Jan 07, 2013 12:23 AM
  • Edited by decker dong - msft on Jan 08, 2013 07:38 AM
  • Marked as answer by decker dong - msft on Jan 08, 2013 07:38 AM
• • Reply
  

  jcmartin

  Member

  2 Points

  27 Posts

  Re: xml with column and cell names to gridview or table

  Jan 06, 2013 12:45 PM|LINK

  Sucess, thanks!

  Changed

  For Each item As var In columns
  ...

  For Each item As var In doc.Descendants("row")

  to 

  For Each item In columns
  ...

  For Each item In doc.Descendants("row")

  • Edited by jcmartin on Jan 06, 2013 12:59 PM
  • Edited by decker dong - msft on Jan 07, 2013 12:23 AM
  • Edited by decker dong - msft on Jan 08, 2013 07:38 AM
  • Marked as answer by Steven Cheng - MSFT on Jan 11, 2013 07:44 AM