Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Parse XML Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 6:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 4, 2014 11:40 PM
Points: 33, Visits: 82
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:

Title ReleaseDate Updated ItemCount
Paradise Valley - John Mayer 2013-08-28T03:55:45-07:00 2013-08-16T00:00:00-07:00 11
PRISM (Deluxe Version) - Katy Perry 2013-08-28T03:55:45-07:00 2013-10-21T00:00:00-07:00 16

Thanks!
Post #1489155
Posted Wednesday, August 28, 2013 6:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:53 AM
Points: 1,678, Visits: 19,555
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)



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1489163
Posted Wednesday, August 28, 2013 9:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 4, 2014 11:40 PM
Points: 33, Visits: 82
Thank you!!
Post #1489509
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse