LINQ to XML to Datatable

Question

i've the following xml fomat

<feed>
 <entry>
   <s:product>
   <s:author>
    <s:name>amazone</s:name>
    <s:accountId>1111</s:accountId>
   </s:author>
   <s:title>xxx
   </s:title>
   <s:condition>used
   </s:condition>
   <s:inventories>
    <s:inventory channel="online" availability="inStock">
     <s:price shipping="4.9" currency="EUR">14.95</s:price>
    </s:inventory>
   </s:inventories>
  </s:product>
 </entry>
 <entry>
  <s:product>
   <s:author>
    <s:name>ebay</s:name>
    <s:accountId>1221</s:accountId>
   </s:author>
   <s:title>yyy
   </s:title>
   <s:condition>new
   </s:condition>
   <s:inventories>
    <s:inventory channel="online" availability="inStock">
     <s:price shipping="4.90" currency="EUR">99.95</s:price>
    </s:inventory>
   </s:inventories>
  </s:product>
 </entry>  
</feed>

i've been trying to put it in a datatable that will look like the following

name       condition       price      shipping      totalprice      availablity
amazone    used            14.95      4.95          19.90           inStock 
ebay       new             99.95      4.90          104.85          inStock 

as you can see each <entry> is supposed to be a row, and i don't want the values of all tags, just the following

column name       :value of <s:name>      
column condition  :value of <s:condition> 
column sprice     :value of <s:price>
column shipping   :value of shipping which is an attribute of <s:price>
column totalprice :value of <s:price> + its attribute shipping
column avilability:value of avialability an attribute of <s:inventory>

how can i achive this using LINQ?

Answer 1

There's a whole lot going on in your code there, most of it not good. You're just simply not using the LINQ to XML library properly.

• Your XML document and the data you're interested in have namespaces. You left out the namespace declaration in the document and you don't access any of the elements properly.

  You need to create an XNamespace variable set to the namespace used in the document and use it when you are trying to reference the appropriate elements.

• You can't just cast the value of an element/attribute (a string) to a double like that, you would have to parse it in that case. However LINQ to XMl provides explicit casts which handles the conversions. So you cast to the appropriate type on the element/attribute itself, not the value they contain. Use this instead of manipulating the Value.

  On a side note, whenever you deal with currency, never use double (or any other floating point type), use decimal.

• You cannot create instances of data rows without creating one from the data table. It is advisable to have your queries should be concerned about the data first. Creating new data rows for a table Once you have the data, then worry about creating the rows and filling the data.

With that said, here's how you would use it more properly.

XDocument doc = GetDocument();
// assuming this is the correct namespace
XNamespace s = "http://www.google.com/shopping/api/schemas/2010";
var query =
    from product in doc.Root.Elements("entry").Elements(s + "product")
    // declare some variables to make the query cleaner
    let inventory = product.Element(s + "inventories").Element(s + "inventory")
    let price = (decimal)inventory.Element(s + "price")
    let shipping = (decimal)inventory.Element(s + "price").Attribute("shipping")
    select new
    {
        Name = (string)product.Element(s + "author").Element(s + "name"),
        Condition = (string)product.Element(s + "condition"),
        Price = price,
        Shipping = shipping,
        TotalPrice = price + shipping,
        Availability = (string)inventory.Attribute("availability"),
    };

var dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Condition", typeof(string));
dt.Columns.Add("Price", typeof(decimal));
dt.Columns.Add("Shipping", typeof(decimal));
dt.Columns.Add("TotalPrice", typeof(decimal));
dt.Columns.Add("Availability", typeof(string));
foreach (var product in query)
{
    dt.Rows.Add(
        product.Name,
        product.Condition,
        product.Price,
        product.Shipping,
        product.TotalPrice,
        product.Availability
    );
}