Importing XML (iTunes Library) into MS SQL
Page 1 of 1
20101229
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.
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.
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.
And another one I use creates a Top 15 of track per artist, including covers from other bands. That query looks like this.
And to give an idea on how this looks my Metallica Top 5
One giving me a problem was Guns N’ Roses, because of the ‘ symbol. I solved this by using a wildcard and LIKE statement.
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.
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
Metallica | 2849 |
Amorphis | 2105 |
Fear Factory | 1319 |
Disturbed | 1095 |
Guns N' Roses | 1088 |
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
Metallica | The Day That Never Comes | 149 |
Amorphis | Alone | 143 |
Amorphis | The Smoke | 124 |
Paradise Lost | The Enemy | 116 |
Metallica | One | 110 |
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 Comes | Metallica | 149 |
One | Metallica | 110 |
Fade To Black | Metallica | 108 |
Enter Sandman | Metallica | 91 |
Nothing Else Matters | Metallica | 89 |
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.
thierry- Posts : 2238
Join date : 2009-08-18
Age : 42
Location : Zoetermeer
Permissions in this forum:
You cannot reply to topics in this forum
|
|