Parse XML

  • Hello! I have some XML I need to parse and am having trouble sorting out how. I will need to parse a few hundred rows at a time and each row contains XML like the following snippet:

    <feed xmlns:im="http://itunes.apple.com/rss" xmlns="http://www.w3.org/2005/Atom" xml:lang="en">

    <id>https://itunes.apple.com/AU/rss/topalbums/limit=10/xml</id&gt;

    <title>iTunes Store: Top Albums</title>

    <entry>

    <updated>2013-08-28T03:55:45-07:00</updated>

    <title>Paradise Valley - John Mayer</title>

    <im:releaseDate label="16 Aug 2013">2013-08-16T00:00:00-07:00</im:releaseDate>

    <im:itemCount>11</im:itemCount>

    </entry>

    <entry>

    <updated>2013-08-28T03:55:45-07:00</updated>

    <title>PRISM (Deluxe Version) - Katy Perry</title>

    <rights>℗ 2013 Capitol Records, LLC</rights>

    <im:releaseDate label="21 Oct 2013">2013-10-21T00:00:00-07:00</im:releaseDate>

    <im:itemCount>16</im:itemCount>

    </entry>

    </feed>

    And I'd like the results for each row to return results like this:

    TitleReleaseDate Updated ItemCount

    Paradise Valley - John Mayer2013-08-28T03:55:45-07:00 2013-08-16T00:00:00-07:0011

    PRISM (Deluxe Version) - Katy Perry 2013-08-28T03:55:45-07:00 2013-10-21T00:00:00-07:0016

    Thanks!

  • Try this

    DECLARE @x XML = '

    <feed xmlns:im="http://itunes.apple.com/rss" xmlns="http://www.w3.org/2005/Atom" xml:lang="en">

    <id>https://itunes.apple.com/AU/rss/topalbums/limit=10/xml</id&gt;

    <title>iTunes Store: Top Albums</title>

    <entry>

    <updated>2013-08-28T03:55:45-07:00</updated>

    <title>Paradise Valley - John Mayer</title>

    <im:releaseDate label="16 Aug 2013">2013-08-16T00:00:00-07:00</im:releaseDate>

    <im:itemCount>11</im:itemCount>

    </entry>

    <entry>

    <updated>2013-08-28T03:55:45-07:00</updated>

    <title>PRISM (Deluxe Version) - Katy Perry</title>

    <rights>? 2013 Capitol Records, LLC</rights>

    <im:releaseDate label="21 Oct 2013">2013-10-21T00:00:00-07:00</im:releaseDate>

    <im:itemCount>16</im:itemCount>

    </entry>

    </feed>';

    WITH XMLNAMESPACES(default 'http://www.w3.org/2005/Atom', 'http://itunes.apple.com/rss' AS im)

    SELECT x.r.value('(title/text())[1]','VARCHAR(30)') AS Title,

    x.r.value('(im:releaseDate/text())[1]','DATETIME') AS ReleaseDate,

    x.r.value('(updated/text())[1]','DATETIME') AS Updated,

    x.r.value('(im:itemCount/text())[1]','INT') AS ItemCount

    FROM @x.nodes('/feed/entry') AS x(r)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Thank you!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply