I need to return a single row for each 'asset' with the parentId taken
from the details node. This is what I have so far

DECLARE @myXml AS XML

SET @myXml =
'<cacheassets>
<assetDetails>
<AssetCollection code="0" msg="" id="0" page="1" pageSize="100"
totalItems="1">
<detail d4p1:type="MusicAsset" id="11546" position="0"
isCol="true" canBuy="false" contentProviderId="1" type="CloudMusic"
xmlns:d4p1="http://www.w3.org/2001/XMLSchema-instance">
<music title="X &amp; Y" thumbnail="http://cdn.7static.com/
static/img/sleeveart/00/000/115/0000011546_50.jpg" artist="Coldplay"
label="" version="" explicit="false" duration="0" purchaseDate=""
releaseDate="2006-08-14T00:00:00+01:00" musicType="Album" />
</detail>
<asset d4p1:type="MusicAsset" id="118133" parentAsset=""
position="7" isCol="false" canBuy="false" contentProviderId="1"
type="CloudMusic" dlRem="3" xmlns:d4p1="http://www.w3.org/2001/
XMLSchema-instance">
<music title="Speed Of Sound" thumbnail="" artist="Coldplay"
version="" explicit="false" duration="288"
purchaseDate="2011-10-06T15:04:08+01:00" releaseDate=""
musicType="Track">
<formats>
<format id="17" type="MP3" bitRate="320" drmFree="true" />
</formats>
</music>
</asset>
</AssetCollection>
<AssetCollection code="0" msg="" id="0" page="1" pageSize="100"
totalItems="2">
<detail d4p1:type="MusicAsset" id="1425249" position="0"
isCol="true" canBuy="false" contentProviderId="1" type="CloudMusic"
xmlns:d4p1="http://www.w3.org/2001/XMLSchema-instance">
<music title="Wherever You Will Go" thumbnail="http://cdn.
7static.com/static/img/sleeveart/00/014/252/0001425249_50.jpg"
artist="Charlene Soraia" label="" version="" explicit="false"
duration="0" purchaseDate="" releaseDate="2011-09-30T00:00:00+01:00"
musicType="Single" />
</detail>
<asset d4p1:type="MusicAsset" id="15720039" parentAsset=""
position="1" isCol="false" canBuy="false" contentProviderId="1"
type="CloudMusic" dlRem="9" xmlns:d4p1="http://www.w3.org/2001/
XMLSchema-instance">
<music title="Wherever You Will Go" thumbnail=""
artist="Charlene Soraia" version="" explicit="false" duration="199"
purchaseDate="2011-11-11T14:46:16Z" releaseDate="" musicType="Track">
<formats>
<format id="17" type="MP3" bitRate="320" drmFree="true" />
</formats>
</music>
</asset>
<asset d4p1:type="MusicAsset" id="15720045" parentAsset=""
position="2" isCol="false" canBuy="false" contentProviderId="1"
type="CloudMusic" dlRem="6" xmlns:d4p1="http://www.w3.org/2001/
XMLSchema-instance">
<music title="Lightyears" thumbnail="" artist="Charlene
Soraia" version="" explicit="false" duration="183"
purchaseDate="2011-11-11T14:46:16Z" releaseDate="" musicType="Track">
<formats>
<format id="17" type="MP3" bitRate="320" drmFree="true" />
</formats>
</music>
</asset>
</AssetCollection>
</assetDetails>
</cacheassets>
'

SELECT Assets.Asset.value('(detail/@id)[1]', 'varchar(255)') as
parentId,
Assets.Asset.query('asset') as asset
FROM @myXml.nodes('/cacheassets/assetDetails/AssetCollection') as
Assets(Asset)

However it returns a single line for each parentId with all the assets
in the XML field. How do I split them into seperate rows.