Miscellany

select track_id into #xmlexport 
from tracks where track_id in (
-- Put comma seperated track_ids here
12345,123456
)


SELECT 1 AS Tag, NULL AS Parent,
	NULL AS [tracks!1!] ,
	NULL AS [track!2!track_id!element] ,
	NULL AS [track!2!title!element] ,
	NULL AS [track!2!artist_name!element] ,
	NULL AS [track!2!duration!element] ,
	NULL AS [genres!3!element] ,
	NULL AS [genre!4!genre_id!element] ,
	NULL AS [genre!4!genre_name!element]

UNION ALL

SELECT 2 AS Tag, 1 AS Parent,
	NULL AS [tracks!1!element] ,
	t.track_id AS [track!2!track_id!element] ,
	t.title AS [track!2!title!element] ,
	t.artist_name AS [track!2!artist_name!element] ,
	t.duration AS [track!2!duration!element] ,
	NULL AS [genres!3!element] ,
	NULL AS [genre!4!genre_id!element] ,
	NULL AS [genre!4!genre_name!element]

FROM tracks t
where t.track_id in ( select track_id from #xmlexport )

UNION ALL

SELECT 3 AS Tag , 2 AS Parent,
	NULL AS [tracks!1!element] ,
	t.track_id AS [track!2!track_id!element] ,
	t.title AS [track!2!title!element] ,
	t.artist_name AS [track!2!artist_name!element] ,
	t.duration AS [track!2!duration!element] ,
	NULL AS [genres!3!element] ,
	NULL AS [genre!4!genre_id!element] ,
	NULL AS [genre!4!genre_name!element]

FROM 
	tracks t
WHERE t.track_id in ( select track_id from #xmlexport )

UNION ALL

SELECT 4 AS Tag , 3 AS Parent,
	NULL AS [tracks!1!element] ,
	t.track_id AS [track!2!track_id!element] ,
	t.title AS [track!2!title!element] ,
	t.artist_name AS [track!2!artist_name!element] ,
	t.duration AS [track!2!duration!element] ,
	NULL AS [genres!3!element] ,
	g.genre_id AS [genre!4!genre_id!element] ,
	g.genre_name AS [genre!4!genre_name!element]

FROM 
	tracks t
	INNER JOIN track_genre_xref tgx on tgx.track_id = t.track_id
	INNER JOIN genres g on g.genre_id = tgx.genre_id
WHERE t.track_id in ( select track_id from #xmlexport )

FOR XML EXPLICIT 

drop table #xmlexport