read data from xml file using LINQ? RSS

• • Reply
  

  jeyaseelan@a...

  Contributor

  5124 Points

  2025 Posts

  read data from xml file using LINQ?

  Jan 16, 2009 04:44 AM|LINK

  Hi to all,

  I have a XML file i want to select records from that but i dont know what are the columns is there because while runtime the xml file columns are differ, so how can i write the query for this situation using LINQ?

  Experts please help me.

   

  Any doubts please feel free to ask me. If this post is answer of your question then don't forgot to Click "Mark As Answer".
  J.Jeyaseelan
  • Moved by anas on Jan 16, 2009 10:17 AM
• • Reply
  

  Martin_Honne...

  Star

  14481 Points

  2006 Posts

  MVP

  Re: read data from xml file using LINQ?

  Jan 16, 2009 11:13 AM|LINK

  Well an XML document does not have records or columns, instead it has nodes like element nodes, attribute nodes, comment nodes, text nodes. You can read out such nodes and determine the names if you want. Here is an example that recursively processes the elements nodes, starting with the root element and then processing the child elements:

  With the XML being

   

  <root>
    <foo>
      <bar>bar 1</bar>
    </foo>
    <foo>
      <bar>bar 2</bar>
    </foo>
  </root>

    

   

  this C# snippet

          static void Main()
          {
              XDocument doc = XDocument.Load(@"..\..\XMLFile1.xml");
              ShowChildren(doc.Root);
          }
          static void ShowChildren(XElement el)
          {
              ShowChildren(el, "");
          }
          static void ShowChildren(XElement el, string indent)
          {
              Console.WriteLine(indent + "Element named '{0}'.", el.Name);
              foreach (XElement childEl in el.Elements())
              {
                  ShowChildren(childEl, indent + "  ");
              }
          }

   outputs

  Element named 'root'.
    Element named 'foo'.
      Element named 'bar'.
    Element named 'foo'.
      Element named 'bar'.

  If that does not help then you need to show us samples of the XML you want to process and tell us exactly which nodes you are interested in.

  Martin Honnen --- MVP Data Platform Development
  My blog
• • Reply
  

  jeyaseelan@a...

  Contributor

  5124 Points

  2025 Posts

  Re: read data from xml file using LINQ?

  Jan 16, 2009 11:40 AM|LINK

  Martin_Honnen,

  Thanks for your response.

  Actually this is my xml file, i want to retrieve these data and bind to datagridview control

  <?xml version="1.0" standalone="yes"?>
  <NewDataSet>
    <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
      <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="Table" msdata:UseCurrentLocale="true">
        <xs:complexType>
          <xs:choice minOccurs="0" maxOccurs="unbounded">
            <xs:element name="Table">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name="id" type="xs:int" minOccurs="0" />
                  <xs:element name="name" type="xs:string" minOccurs="0" />
                  <xs:element name="age" type="xs:int" minOccurs="0" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:choice>
        </xs:complexType>
      </xs:element>
    </xs:schema>
    <Table>
      <id>1</id>
      <name>mary</name>
      <age>23</age>
    </Table>
    <Table>
      <id>2</id>
      <name>joseph</name>
      <age>34</age>
    </Table>

  I write this method in the code behind

  public void Method4()

  {

  try

  {

  XDocument xdoc = XDocument.Load("Simple.xml");

   

  DataSet ds = new DataSet();

  DataTable dt = new DataTable();

  var MyOutput = from MyTable in xdoc.Elements("NewDataSet").Elements("Table")

   

  select MyTable;

   

   

  dt = MyOutput.CopyToDataTable();

  dataGridView1.DataSource = dt;

  //BindingSource bs = new BindingSource();

  //bs.DataSource = MyOutput;

  //dataGridView1.DataSource = bs;

   

  }

  catch (Exception ex)

  {

  label1.Text = ex.Message;

  }

  }

  but the output like this

   

  EmptySequence	FirstAttribute	HasAttributes	HasElements	IsEmpty
  		□(UnCheckbox)	□(Checkedbox)	□(UnCheckbox)
  		□(UnCheckbox)	□(Checkedbox)	□(UnCheckbox)

   

  why it looks like this, could you please tell me the solution?

  Any doubts please feel free to ask me. If this post is answer of your question then don't forgot to Click "Mark As Answer".
  J.Jeyaseelan
• • Reply
  

  Martin_Honne...

  Star

  14481 Points

  2006 Posts

  MVP

  Re: read data from xml file using LINQ?

  Jan 16, 2009 11:57 AM|LINK

  Why do you want to use LINQ to XML with that XML document? It is tailor made for simply reading it into a DataSet with ReadXml e.g.

  DataSet ds = new DataSet();

  ds.ReadXml("Simple.xml");

  dataGridView1.DataSource = ds.Tables[0];

  Martin Honnen --- MVP Data Platform Development
  My blog
• • Reply
  

  jeyaseelan@a...

  Contributor

  5124 Points

  2025 Posts

  Re: read data from xml file using LINQ?

  Jan 16, 2009 12:13 PM|LINK

  Martin_Honnen,

  Thanks for your response.

  Actually this is my task(read xml data by LINQ and bind to dgv), could you please tell me how to do this?

  Any doubts please feel free to ask me. If this post is answer of your question then don't forgot to Click "Mark As Answer".
  J.Jeyaseelan
• • Reply
  

  jeyaseelan@a...

  Contributor

  5124 Points

  2025 Posts

  Re: read data from xml file using LINQ?

  Jan 16, 2009 12:36 PM|LINK

  Martin_Honnen, thanks for ur response.

  If i do like this,

  DataSet ds = new DataSet();

  ds.ReadXml("Simple.xml");

  dataGridView1.DataSource = ds.Tables[0];

  now i want to filter the data from ds using LINQ could you please tell me how to write the query without mention column name?

  Any doubts please feel free to ask me. If this post is answer of your question then don't forgot to Click "Mark As Answer".
  J.Jeyaseelan
• • Reply
  

  Martin_Honne...

  Star

  14481 Points

  2006 Posts

  MVP

  Re: read data from xml file using LINQ?

  Jan 16, 2009 12:39 PM|LINK

  Well if you know which elements you want to access then you can easily create an anonymous type for instance:

   

  XDocument doc = XDocument.Load("XMLFile1.xml");
              dataGridView1.DataSource =
                                  (from table in doc.Root.Elements("Table")
                                  select new
                                  {
                                      Id = (int)table.Element("id"),
                                      Name = (string)table.Element("name"),
                                      Age = (int)table.Element("age")
                                  }).ToList();

   

  I realize that does not help if your original requirement of not knowing the XML structure is still valid.

  Martin Honnen --- MVP Data Platform Development
  My blog
  • Marked as answer by jeyaseelan@ajsquare.net on Jan 19, 2009 02:43 AM
• • Reply
  

  jeyaseelan@a...

  Contributor

  5124 Points

  2025 Posts

  Re: read data from xml file using LINQ?

  Jan 16, 2009 12:47 PM|LINK

  Martin_Honnen

  thanks for ur response.

  Actually i dont know how many columns will come on xml file so cant use this query

  select new
                                  {
                                      Id = (int)table.Element("id"),
                                      Name = (string)table.Element("name"),
                                      Age = (int)table.Element("age")
                                  }).ToList();

  so i accept ur idea that is

  DataSet ds = new DataSet();

  ds.ReadXml("Simple.xml");

  dataGridView1.DataSource = ds.Tables[0];

  now i want to filter the records from ds by LINQ query without mentioning column name(column name will com from combobox)

  could you please tell me how to do this?

  Thanks in advance.

  Any doubts please feel free to ask me. If this post is answer of your question then don't forgot to Click "Mark As Answer".
  J.Jeyaseelan
• • Reply
  

  jeyaseelan@a...

  Contributor

  5124 Points

  2025 Posts

  Re: read data from xml file using LINQ?

  Jan 19, 2009 03:57 AM|LINK

  Martin_Honnen can you please answer my question?
  Any doubts please feel free to ask me. If this post is answer of your question then don't forgot to Click "Mark As Answer".
  J.Jeyaseelan
• • Reply
  

  jeyaseelan@a...

  Contributor

  5124 Points

  2025 Posts

  Re: read data from xml file using LINQ?

  Jan 19, 2009 11:00 AM|LINK

  Martin Actually i found one code

  Dynamic Expression API

  The Dynamic Expression API is brought into scope by using (importing) the System.Linq.Dynamic namespace. Below is an example of applying the Dynamic Expression API to a LINQ to SQL data source.

  var query =
      db.Customers.
      Where("City = @0 and Orders.Count >= @1", "London", 10).
      OrderBy("CompanyName").
      Select("new(CompanyName as Name, Phone)");

  but this will work if i include dbml file. if i change the query like my format

  DataTable dtTemp = new DataTable();

  var query =
      dtTemp.AsEnumerable().
      Where("Name= @0 ", "yova").
      OrderBy("Name").
      Select("new(Name)");

  dtTemp = query.CopyToDataTable();

  but it raise the following error at bolded line

  Error 1 'System.Data.EnumerableRowCollection<System.Data.DataRow>' does not contain a definition for 'Where' and the best extension method overload 'System.Linq.Dynamic.DynamicQueryable.Where(System.Linq.IQueryable, string, params object[])' has some invalid arguments D:\\Linq\LINQ_Final_POC\LINQ Demo\WindowsFormsApplication1\MyWork\Form1.cs 98 29 WindowsFormsApplication1

  Error 2 Instance argument: cannot convert from 'System.Data.EnumerableRowCollection<System.Data.DataRow>' to 'System.Linq.IQueryable' D:\\Linq\LINQ_Final_POC\LINQ Demo\WindowsFormsApplication1\MyWork\Form1.cs 98 29 WindowsFormsApplication1

  Error 3 Argument '3': cannot convert from 'string' to 'object[]' D:\\Linq\LINQ_Final_POC\LINQ Demo\WindowsFormsApplication1\MyWork\Form1.cs 98 29 WindowsFormsApplication1

  Experts please help me, how to do this?
  

   

  Any doubts please feel free to ask me. If this post is answer of your question then don't forgot to Click "Mark As Answer".
  J.Jeyaseelan