|
|
|
|
Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.
So I was trying to figure out a way to get my iTunes/iPod music library into SQL Server. Unfortunately, the XML in the library is not the easiest with which to work. Here is an abridged version of that XML:
< plist version="1.0">
<dict>
<key>Major Version</key>
<integer>1</integer>
<key>Application Version</key>
<string>7.0.2</string>
<key>Show Content Ratings</key>
<true />
<key>Tracks</key>
<dict>
<key>1288</key>
<dict>
<key>Track ID</key>
<integer>1288</integer>
<key>Name</key>
<string>Brighter Than Sunshine</string>
<key>Artist</key>
<string>Aqualung</string>
<key>Album Artist</key>
<string>Aqualung</string>
<key>Album</key>
</dict>
</dict>
</dict>
</plist>
It uses a name-value pairing of the nodes, which would not have been my first choice is the schema. I would have gone with something more like this:
< plist version="1.0">
<key name="Major Version" value="1" type="integer"/>
<key name="Application Version" value="7.0.2" type="string"/>
<key name="Show Content Ratings" value="true" type="boolean" />
<tracks>
<track id="1288">
<key name="TrackID" value="1288" type="integer"/>
<key name="Name" value="Brighter Than Sunshine" type="string"/>
<key name="Artist" value="Aqualung" type="string"/>
<key name="Album Artist" value="Aqualung" type="string"/>
<key name="Album" value="Strange & Beautiful" type="string"/>
</track>
</tracks>
</plist>
I digress. The point here is how do I get the data from the exported library file (File... Export Library... in iTunes). It was an interesting task and the solution used the XML, CTE, and PIVOT features of SQL Server 2005
DECLARE @x xml
CREATE TABLE #t (LibraryID int identity(1, 1), Library xml)
-- Load the XML, you will need to do the convert to drop the DTD refs
-- and load without errors
SET @X = (SELECT CONVERT(xml, BulkColumn, 2) FROM OPENROWSET
(BULK N'C:\Library.xml',
SINGLE_BLOB) AS Document)
-- Use the nodes method to extract the tracks
INSERT INTO #t (library)
SELECT T.C.query('.') AS SongInfo
FROM @x.nodes('/plist[1]/dict[1]/dict[1]/dict') AS T(C)
-- Generate the name-value pairs
;WITH kvps AS
(SELECT library.value('/dict[1]/integer[1]', 'int') AS [ID],
t.c.value('local-name(.)', 'varchar(255)') AS [type],
t.c.value('.', 'varchar(255)') AS [value],
t.c.value('for $s in . return count(../*[. << $s]) + 1', 'int') AS RowNum
-- this last one generates a row number for the returned nodes
FROM #t
CROSS APPLY library.nodes('/dict/*') AS T(C))
, final AS
(SELECT k.id,
k2.RowNum / 2 AS [RowNum],
k.value AS [Key],
CASE WHEN k2.[value] IS NOT NULL AND k2.[value] != ''
THEN k2.[value] ELSE k2.type END AS [Value],
CASE WHEN k2.[value] IS NOT NULL AND k2.[value] != ''
THEN k2.[type] ELSE 'bit' END AS [Type]
FROM kvps AS k
INNER JOIN kvps AS k2
ON k.rownum + 1 = k2.rownum AND k.id = k2.id
WHERE k2.rownum > 1 AND k2.rownum % 2 = 0)
SELECT *
INTO #t2
FROM final
-- Pivot the results
;WITH t2 AS (SELECT [id], [key], [value] FROM #t2)
SELECT [id], [Name], [Artist], [Album Artist], [Album], [Genre],
[Kind], [Size], [Total Time],
IsNull([Disc Number], 1) AS [Disc Number],
IsNull([Disc Count], 1) AS [Disc Count],
IsNull([Track Number], 1) AS [Track Number],
[Track Count], [Year], [Date Modified],
[Date Added], [Bit Rate], [Sample Rate]
INTO #t3
FROM t2
PIVOT
(MAX([Value])
FOR [key] IN
([Name], [Artist], [Album Artist], [Album], [Genre], [Kind],
[Size], [Total Time], [Disc Number], [Disc Count],
[Track Number], [Track Count], [Year], [Date Modified],
[Date Added], [Bit Rate], [Sample Rate])
) AS pvt
-- Viola - you have a list of tracks
SELECT * FROM #t3
ORDER BY [Artist], [Album], [Disc Number], [Track Number]
--DROP TABLE #t
--DROP TABLE #t2
--DROP TABLE #t3
If you want to make the data more permanent, don't use temporary tables.
Now this will take some time to run, depending on how many songs you actually have. My collection of 13,851 songs took 5 1/2 minutes to run this code and generate my results. Mind you, the XML file is 20 MB and the joins are awful. However, some indexes would make this go faster.
The cool thing is that you could take this concept to the next level using other technologies (such as SSIS) in order to keep your SQL database up to date.
FYI: I included the SQL file that contains all the code in this post.
Enjoy!
--Peter
New Comments to this post are disabled
About Peter W. DeBetta
Peter DeBetta works as a Data and Cloud Solution Manager at DB Best, a Microsoft gold partner company. Peter has written courseware, articles, and books on topics such as development and SQL Server. Peter has spoken at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive! When Peter isn’t working, you can find him singing and playing guitar, taking pictures, working on DIY projects, woodworking, or simply enjoying life with his kids.
|
|
|
|
|