tags:

views:

28

answers:

2
Q: 

Parse XML using LINQ to SQL

Im trying to parse the following XML using LINQ-to-SQL but visual studio doesn't seem to want to play ball. I've tried using XElement and XDocument and tried selecting from the root node or the api_item node directly, but doesn't seem to happen for me.

Can anyone advise a bit of LINQ-to-XML?

I've tried the following (and many variations of it!) to no avail. (note: e.Result contains the XML string)

var deals = from el in XElement.Parse(e.Result).Elements("api_response").Elements("deals").Elements("api_item")
                        select new
                        {
                            title = el.Element("title").Value,
                            description = el.Element("description").Value

                        };

Thanks! (heres a snippet of the XML coming back from the API)

         <?xml version="1.0" ?> 
    - <api_response>
    - <deals>
    - <api_item>
      <title>Palit GeForce GTX460 768MB: £143.56 at CCLOnline</title> 
      <deal_link>http://www.hotukdeals.com/deals/palit-geforce-gtx460-768mb-143-56-a/725851&lt;/deal_link&gt; 
      <mobile_deal_link>http://m.hotukdeals.com/deals/palit-geforce-gtx460-768mb-143-56-a/725851&lt;/mobile_deal_link&gt; 
      <deal_image>http://www.hotukdeals.com/images/threads/725851_1.jpg&lt;/deal_image&gt; 
      <description>Palit GeForce GTX460 768MB - NE5TX460FHD79 (Free delivery or collection) Graphics card require: ..PCI Express or PCI Express 2.0-compliant motherboard with one dual-width x16 graphics slot ..Two 6-pin PCI Express supplementary power connectors ..Minimum 450W or greater system power supply (with a minimum 12V current rating of 24A) ..Supported Operating Systems: Windows 7, Windows Vista, Windows XP</description> 
      <submit_time>19 hours, 29 minutes ago</submit_time> 
      <hot_time>8 minutes ago</hot_time> 
      <poster_name>gads</poster_name> 
      <temperature>102.629997253</temperature> 
      <price>143.56</price> 
      <timestamp>1279921047</timestamp> 
      <expired>false</expired> 
    - <forum>
      <name>Deals</name> 
      <url_name>deals</url_name> 
      </forum>
    - <category>
      <name>Computers</name> 
      <url_name>computers</url_name> 
      </category>
    - <merchant>
      <name>CCLOnline</name> 
      <url_name>cclonline</url_name> 
      </merchant>
      <tags /> 
      </api_item>
    - <api_item>
      <title>Empire Total War (PC) £10 instore @ HMV!</title> 
      <deal_link>http://www.hotukdeals.com/deals/empire-total-war-pc-10-instore-hmv/725848&lt;/deal_link&gt; 
      <mobile_deal_link>http://m.hotukdeals.com/deals/empire-total-war-pc-10-instore-hmv/725848&lt;/mobile_deal_link&gt; 
      <deal_image>http://www.hotukdeals.com/images/threads/725848_1.jpg&lt;/deal_image&gt; 
      <description>Not the latest game in the series, but still a cracking game, and about a fiver cheaper than the best online price by the looks of things.</description> 
      <submit_time>19 hours, 34 minutes ago</submit_time> 
      <hot_time>8 minutes ago</hot_time> 
      <poster_name>Crazy Jamie</poster_name> 
      <temperature>98.4000015259</temperature> 
      <price>10</price> 
      <timestamp>1279920760</timestamp> 
      <expired>false</expired> 
    - <forum>
      <name>Deals</name> 
      <url_name>deals</url_name> 
      </forum>
    - <category>
      <name>Entertainment</name> 
      <url_name>entertainment</url_name> 
      </category>
    - <merchant>
      <name>HMV</name> 
      <url_name>hmv</url_name> 
      </merchant>
    - <tags>
    - <api_item>
      <name>empire total war</name> 
      </api_item>
      </tags>
      </api_item>
    - <api_item>
      <title>Samsung SH-B083L 8x Bluray ROM + DVD-RW - £46.72 Delivered or LESS @ Overclockers</title> 
      <deal_link>http://www.hotukdeals.com/deals/samsung-sh-b083l-8x-bluray-rom-dvd-/725984&lt;/deal_link&gt; 
      <mobile_deal_link>http://m.hotukdeals.com/deals/samsung-sh-b083l-8x-bluray-rom-dvd-/725984&lt;/mobile_deal_link&gt; 
      <deal_image>http://www.hotukdeals.com/images/threads/725984_1.jpg&lt;/deal_image&gt; 
      <description>If you get free delivery from Overclockers by being a long-time forums member then its just £43.46 delivered and its the same price if you buy from their shop in Stoke. Samsung's SH-B083L can play Blu-ray discs and record to CDs and DVDs. Plus, it has fast read speeds, which go all the way up to 8x. In addition to 1080p HD picture support , the SHB083L is available with a huge capacity of up to 25 GB for a single-layer disc, and 50 GB for a dual-layer disc. You can also breathe new life into your current movie collection with the latest upscaling technology this gives your standard definition DVDs a dramatically much improved high resolution look. Write Speed - DVD-R: 16x - DVD-R DL: 8x - DVD+R: 16x - DVD+R DL: 8x - DVD+RW: 6x - DVD-RAM: 12x - CD-R: 48x - CD-RW: 4x Read Speed - BD-R: 8x - BD-R DL: 4x - BD-RE DL: 4x - DVD-RAM: 12x - DVD-ROM: 16x - DVD-R: 16x - DVD+R: 16x - DVD+RW: 12x - CD-R: 48x - CD-RW: 40x - CD-ROM: 48x</description> 
      <submit_time>6 hours, 38 minutes ago</submit_time> 
      <hot_time>9 minutes ago</hot_time> 
      <poster_name>hajj_3</poster_name> 
      <temperature>96.4800033569</temperature> 
      <price>46.72</price> 
      <timestamp>1279967326</timestamp> 
      <expired>false</expired> 
    - <forum>
      <name>Deals</name> 
      <url_name>deals</url_name> 
      </forum>
    - <category>
      <name>Computers</name> 
      <url_name>computers</url_name> 
      </category>
    - <merchant>
      <name>Overclockers</name> 
      <url_name>overclockers</url_name> 
      </merchant>
      <tags /> 
      </api_item>
  <?xml version="1.0" ?> 
- <api_response>
- <deals>
- <api_item>
  <title>Palit GeForce GTX460 768MB: £143.56 at CCLOnline</title> 
  <deal_link>http://www.hotukdeals.com/deals/palit-geforce-gtx460-768mb-143-56-a/725851&lt;/deal_link&gt; 
  <mobile_deal_link>http://m.hotukdeals.com/deals/palit-geforce-gtx460-768mb-143-56-a/725851&lt;/mobile_deal_link&gt; 
  <deal_image>http://www.hotukdeals.com/images/threads/725851_1.jpg&lt;/deal_image&gt; 
  <description>Palit GeForce GTX460 768MB - NE5TX460FHD79 (Free delivery or collection) Graphics card require: ..PCI Express or PCI Express 2.0-compliant motherboard with one dual-width x16 graphics slot ..Two 6-pin PCI Express supplementary power connectors ..Minimum 450W or greater system power supply (with a minimum 12V current rating of 24A) ..Supported Operating Systems: Windows 7, Windows Vista, Windows XP</description> 
  <submit_time>19 hours, 29 minutes ago</submit_time> 
  <hot_time>8 minutes ago</hot_time> 
  <poster_name>gads</poster_name> 
  <temperature>102.629997253</temperature> 
  <price>143.56</price> 
  <timestamp>1279921047</timestamp> 
  <expired>false</expired> 
- <forum>
  <name>Deals</name> 
  <url_name>deals</url_name> 
  </forum>
- <category>
  <name>Computers</name> 
  <url_name>computers</url_name> 
  </category>
- <merchant>
  <name>CCLOnline</name> 
  <url_name>cclonline</url_name> 
  </merchant>
  <tags /> 
  </api_item>
- <api_item>
  <title>Empire Total War (PC) £10 instore @ HMV!</title> 
  <deal_link>http://www.hotukdeals.com/deals/empire-total-war-pc-10-instore-hmv/725848&lt;/deal_link&gt; 
  <mobile_deal_link>http://m.hotukdeals.com/deals/empire-total-war-pc-10-instore-hmv/725848&lt;/mobile_deal_link&gt; 
  <deal_image>http://www.hotukdeals.com/images/threads/725848_1.jpg&lt;/deal_image&gt; 
  <description>Not the latest game in the series, but still a cracking game, and about a fiver cheaper than the best online price by the looks of things.</description> 
  <submit_time>19 hours, 34 minutes ago</submit_time> 
  <hot_time>8 minutes ago</hot_time> 
  <poster_name>Crazy Jamie</poster_name> 
  <temperature>98.4000015259</temperature> 
  <price>10</price> 
  <timestamp>1279920760</timestamp> 
  <expired>false</expired> 
- <forum>
  <name>Deals</name> 
  <url_name>deals</url_name> 
  </forum>
- <category>
  <name>Entertainment</name> 
  <url_name>entertainment</url_name> 
  </category>
- <merchant>
  <name>HMV</name> 
  <url_name>hmv</url_name> 
  </merchant>
- <tags>
- <api_item>
  <name>empire total war</name> 
  </api_item>
  </tags>
  </api_item>
- <api_item>
  <title>Samsung SH-B083L 8x Bluray ROM + DVD-RW - £46.72 Delivered or LESS @ Overclockers</title> 
  <deal_link>http://www.hotukdeals.com/deals/samsung-sh-b083l-8x-bluray-rom-dvd-/725984&lt;/deal_link&gt; 
  <mobile_deal_link>http://m.hotukdeals.com/deals/samsung-sh-b083l-8x-bluray-rom-dvd-/725984&lt;/mobile_deal_link&gt; 
  <deal_image>http://www.hotukdeals.com/images/threads/725984_1.jpg&lt;/deal_image&gt; 
  <description>If you get free delivery from Overclockers by being a long-time forums member then its just £43.46 delivered and its the same price if you buy from their shop in Stoke. Samsung's SH-B083L can play Blu-ray discs and record to CDs and DVDs. Plus, it has fast read speeds, which go all the way up to 8x. In addition to 1080p HD picture support , the SHB083L is available with a huge capacity of up to 25 GB for a single-layer disc, and 50 GB for a dual-layer disc. You can also breathe new life into your current movie collection with the latest upscaling technology this gives your standard definition DVDs a dramatically much improved high resolution look. Write Speed - DVD-R: 16x - DVD-R DL: 8x - DVD+R: 16x - DVD+R DL: 8x - DVD+RW: 6x - DVD-RAM: 12x - CD-R: 48x - CD-RW: 4x Read Speed - BD-R: 8x - BD-R DL: 4x - BD-RE DL: 4x - DVD-RAM: 12x - DVD-ROM: 16x - DVD-R: 16x - DVD+R: 16x - DVD+RW: 12x - CD-R: 48x - CD-RW: 40x - CD-ROM: 48x</description> 
  <submit_time>6 hours, 38 minutes ago</submit_time> 
  <hot_time>9 minutes ago</hot_time> 
  <poster_name>hajj_3</poster_name> 
  <temperature>96.4800033569</temperature> 
  <price>46.72</price> 
  <timestamp>1279967326</timestamp> 
  <expired>false</expired> 
- <forum>
  <name>Deals</name> 
  <url_name>deals</url_name> 
  </forum>
- <category>
  <name>Computers</name> 
  <url_name>computers</url_name> 
  </category>
- <merchant>
  <name>Overclockers</name> 
  <url_name>overclockers</url_name> 
  </merchant>
  <tags /> 
  </api_item>
</deals>
  <total_results>1000</total_results> 
  </api_response>
A: 

You can check out LINQ to XML - 5 Minute Overview for a quick tutorial on using that approach.

DOK  2010-07-24 17:41:26
That was where I took code sample above from, but for some reason doesn't seem to work on the above XML
LDJ  2010-07-24 20:38:05
A: 

Figured it out:

var deals = from el in XElement.Parse(e.Result).Elements("deals").Elements("api_item")
            select new
            {
               title = (string)el.Element("title").Value,
               description = (string)el.Element("description").Value
            };

Looks like it won't work unless I cast the Element value before assigning it. Adding the casting seemed to do the trick. How bizarre!

LDJ  2010-07-24 21:00:05

related questions

Merge XML files in a XDocument
LINQ to XML optional element query
convert XmlNode to XNode?
Validating and Extracting XML record by record into Database
Save attribute value of xml element with single quotes using linq to xml.
Linq to XML 'Where not in' syntax problem
How can I combine this code into one or two LINQ queries?
How do I do a deep copy of an element in LINQ to XML?
How do I access the XML data in my column using LINQ to XML?
How can I extract specific tags using linq to xml in F# ?
How do you handle arbitrary namespaces when querying over Linq to XML?
LINQ to XML gets no data with schema set
Is there a way to retrieve elements using only local names in a Linq-to-XML query?
How to get the position() of an XElement?
How to get Single XElement object using Linq to Xml ?
LINQ to XML for a small appliction can it replace a small database?
Issue with XDocument and the BOM (Byte Order Mark)
I need a LINQ expression to find an XElement where the element name and attributes match an input node.
How can I get an XElement's innertext in Linq to XML?
Linq to XML for KML?
LINQ to XML Newbie Question: Returning More Than One Result
LINQ to XML Newbie Question
Do namespaces propagate to children in XElement objects?
How can I extract a part of a xaml object graph via linq to xml?
IntelliSense for XElement objects with XML schema