Importing XML (iTunes Library) into MS SQL

View previous topic View next topic Go down

20101229

Post 

Importing XML (iTunes Library) into MS SQL




Some time ago I was looking for a way to export iTunes track information into a platform like MSSQL to be able to create some fun toplists and stuff. Actually I have more plans to use this in a later stage, but for now I wanted to share this.

First go to your My Music/iTunes and copy the Library.xml file, or export your library using iTunes, to the c root of the machine where you have Microsoft SQL installed. Rename the file if needed to Library.xml.

Next we start the Microsoft SQL Server Management Studio and create a database. Next we select New Query and copy-paste the following there.
Code:
 DECLARE @x xml
CREATE TABLE #t (LibraryID int identity(1, 1), Library xml)

SET @X = (SELECT CONVERT(xml, BulkColumn, 2) FROM OPENROWSET
         (BULK N'C:\Library.xml',
         SINGLE_BLOB) AS Document)

INSERT INTO #t (library)
SELECT  T.C.query('.') AS SongInfo
FROM @x.nodes('/plist[1]/dict[1]/dict[1]/dict') AS T(C)

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

;WITH t2 AS (SELECT [id], [key], [value] FROM #t2)
SELECT [id], [Name], [Artist], [Composer], [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], [Play Count]
INTO iTunes
FROM t2
PIVOT
   (MAX([Value])
   FOR [key] IN
      ([Name], [Artist], [Composer], [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], [Play Count])
   ) AS pvt

DROP TABLE #t
DROP TABLE #t2

Note that I found this somewhere, I only adjusted it

No press the execute button and sit back and relax. Depending on how many files your library contains it can take a while. When it’s finished you will have all your information from the library into SQL, now it is time to run some other queries on it and get some lists. One note, for the queries to work you need to change the Play Count from Varchar to Numeric, else it wont work.

First example is how to make a list of your 25 most played artist. To do this I used the following query.
Code:
 SELECT TOP 25 Artist, SUM([Play Count]) AS [PC]
FROM        iTunes
GROUP BY Artist
ORDER BY [PC] DESC
Ofcourse you can change the 25 for whatever number you like, the query groups every track by artist and sums up the number of plays. Here is an example of the result.
Metallica2849
Amorphis2105
Fear Factory1319
Disturbed1095
Guns N' Roses1088

Another query you could run is one to create a Top 30 most played tracks. Now this is something you could do in iTunes as well, if you don’t, like me, have many duplicate names. I just import my cd’s creating duplicate songs, and using this query I can still get a Top 30 based on unique tracks.

Code:
SELECT TOP 30 Artist, Name, SUM([Play Count]) AS [PC]
FROM        iTunes
GROUP BY Artist, Name
ORDER BY [PC] DESC

MetallicaThe Day That Never Comes149
AmorphisAlone143
AmorphisThe Smoke124
Paradise LostThe Enemy116
MetallicaOne110

And another one I use creates a Top 15 of track per artist, including covers from other bands. That query looks like this.
Code:
 
SELECT TOP 15 Name, Artist, SUM([Play Count]) AS [PC]
FROM        iTunes
WHERE Artist = 'Metallica' OR Composer = 'Metallica'
GROUP BY Artist, Name
ORDER BY [PC] DESC

And to give an idea on how this looks my Metallica Top 5
The Day That Never ComesMetallica149
OneMetallica110
Fade To BlackMetallica108
Enter SandmanMetallica91
Nothing Else MattersMetallica89

One giving me a problem was Guns N’ Roses, because of the ‘ symbol. I solved this by using a wildcard and LIKE statement.
Code:

SELECT TOP 15 Name, Artist, SUM([Play Count]) AS [PC]
FROM        iTunes
WHERE Artist LIKE 'Guns%' OR Composer LIKE 'Guns%'
GROUP BY Artist, Name
ORDER BY [PC] DESC

Still trying to find out how to make a Toplist of the most played Playlists, but this is a bit harder then what I have so far. Any ideas are welcome, and hope you have some use for this. Any questions or suggestions can be placed here.
avatar
thierry

Posts : 2238
Join date : 2009-08-18
Age : 35
Location : Zoetermeer

View user profile

Back to top Go down

- Similar topics
Share this post on: Excite BookmarksDiggRedditDel.icio.usGoogleLiveSlashdotNetscapeTechnoratiStumbleUponNewsvineFurlYahooSmarking

 
Permissions in this forum:
You cannot reply to topics in this forum