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