read data from xml file using LINQ?

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.

Re: read data from xml file using LINQ?

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.

Re: read data from xml file using LINQ?

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?

Re: read data from xml file using LINQ?

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];

Re: read data from xml file using LINQ?

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?

Re: read data from xml file using LINQ?

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?

Re: read data from xml file using LINQ?

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.

Re: read data from xml file using LINQ?

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.

Re: read data from xml file using LINQ?

Martin_Honnen can you please answer my question?

Re: read data from xml file using LINQ?

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?

Re: read data from xml file using LINQ?-Bug?

I think it's not possible or it may be a bug in LINQ

because last two days i'm trying to write a dynamic query to dataset but there is no solution in net and no help document realted to Dynamic Linq to DataSet.

Experts have any idea?

Re: read data from xml file using LINQ?

My task is i have a dataset with N number of records, now i want to retrieve some records from there by user input but in my case i dont know the column name and datatypes of the column because based on situation different no. of column will be added in dataset, so i tried with dynamic linq query by this article http://aspalliance.com/1569_Dynamic_LINQ_Part_1_Using_the_LINQ_Dynamic_Query_Library.all they wrote for SQL

like below

Dim Northwind as new NorthwindDataContext

Dim query = From P in Northwind.Products where ("CategoryId==2 And unitprice>3").OrderBy("SupplierId")

I convert the same syntax to DataSet

var query = ds.Tables[0].AsEnumerable().AsQueryable().Where("name==yova").OrderBy("name");

dataGridView1.DataSource = query;

but it raise an error as No property or field 'name' exists in type 'DataRow' (at index 0)

actually error thrown in yellow colored line, but the column "name" is exist in the DataSet. I tried with different column names but no luck!

What is the problem and how to solve this error experts please explain, i think it's problem in LINQ?