Parse XML using LINQ to SQL

Question

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</deal_link> 
      <mobile_deal_link>http://m.hotukdeals.com/deals/palit-geforce-gtx460-768mb-143-56-a/725851</mobile_deal_link> 
      <deal_image>http://www.hotukdeals.com/images/threads/725851_1.jpg</deal_image> 
      <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</deal_link> 
      <mobile_deal_link>http://m.hotukdeals.com/deals/empire-total-war-pc-10-instore-hmv/725848</mobile_deal_link> 
      <deal_image>http://www.hotukdeals.com/images/threads/725848_1.jpg</deal_image> 
      <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</deal_link> 
      <mobile_deal_link>http://m.hotukdeals.com/deals/samsung-sh-b083l-8x-bluray-rom-dvd-/725984</mobile_deal_link> 
      <deal_image>http://www.hotukdeals.com/images/threads/725984_1.jpg</deal_image> 
      <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</deal_link> 
  <mobile_deal_link>http://m.hotukdeals.com/deals/palit-geforce-gtx460-768mb-143-56-a/725851</mobile_deal_link> 
  <deal_image>http://www.hotukdeals.com/images/threads/725851_1.jpg</deal_image> 
  <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</deal_link> 
  <mobile_deal_link>http://m.hotukdeals.com/deals/empire-total-war-pc-10-instore-hmv/725848</mobile_deal_link> 
  <deal_image>http://www.hotukdeals.com/images/threads/725848_1.jpg</deal_image> 
  <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</deal_link> 
  <mobile_deal_link>http://m.hotukdeals.com/deals/samsung-sh-b083l-8x-bluray-rom-dvd-/725984</mobile_deal_link> 
  <deal_image>http://www.hotukdeals.com/images/threads/725984_1.jpg</deal_image> 
  <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>

Do you have a link to the full XML? That snippet isn't valid XML - so I can't load it into my editor ... — Ron Savage
– Ron Savage, Commented Jul 24, 2010 at 17:36

marc_s · Accepted Answer · 2010-07-24 21:52:03Z

2

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!

edited Jul 24, 2010 at 21:52

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 24, 2010 at 21:00

LDJ

7,35411 gold badges59 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Prashant · Accepted Answer · 2011-12-15 21:37:07Z

1

Try this:

XDocument feed = XDocument.Load("the link for the xml file");
feed.Save("type the link here");            

DataClasses1DataContext db = new DataClasses1DataContext();


var feedpost = from c in feed.Descendants("api_item")
               select new Table_1
               {
                   Title = c.Element("title").Value,
                   description = c.Element("description").Value

               };
db.Table_1s.InsertAllOnSubmit(feedpost);
db.SubmitChanges();

edited Dec 15, 2011 at 21:37

user142162

answered Feb 9, 2011 at 18:54

Prashant

112 bronze badges

Comments

DOK · Accepted Answer · 2010-07-24 17:41:26Z

0

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

answered Jul 24, 2010 at 17:41

DOK

32.8k8 gold badges64 silver badges93 bronze badges

1 Comment

LDJ Over a year ago

That was where I took code sample above from, but for some reason doesn't seem to work on the above XML

Collectives™ on Stack Overflow

Parse XML using LINQ to SQL

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related