Sql for xml explicit - help needed!

[sam aspin]

Hi, can anybody shed any light on what I'm doing wrong here...

I'm trying to acheive an XML that looks like the following:

<tshirt Name="Stussy NY Posse T-Shirt Yellow">
  <variant size="small">
    <productID>10</productID>
    <dimensions length="34" />
  </variant>
  <variant size="medium">
    <productID>11</productID>
    <dimensions length="45" />
  </variant>
  <variant size="large">
    <productID>12</productID>
    <dimensions length="50" />
  </variant>
</tshirt>

Unfortunately, what I'm actually getting is this:

<tshirt Name="Stussy NY Posse T-Shirt Yellow">
  <variant size="small">
    <productID>10</productID>
  </variant>
  <variant size="medium">
    <productID>11</productID>
  </variant>
  <variant size="large">
    <productID>12</productID>
    <dimensions length="34" />
    <dimensions length="45" />
    <dimensions length="50" />
  </variant>
</tshirt>

As you will no doubt notice, all the lengths appear in the 'large' variant when each one is actually associated with the other sizes.

The SQL I'm running is as follows:

declare @productid int
set @productid = 5

SELECT 1 as Tag,
NULL as Parent,
pt.Name	as [tshirt!1!Name],
null	as [variant!2!size],
null	as [variant!2!productID!element],
null	as [variant!2!dimensions!element],
null	as [dimensions!3!length]

from producttemplates pt
where pt.ptid = @productid

UNION ALL

SELECT 2 as Tag,
1 as Parent,
NULL	as [tshirt!1!Name],
s.Size	as [variant!2!size],
p.pid	as [variant!2!productID!element],
null	as [variant!2!dimensions!element],
null	as [dimensions!3!length!element]

from sizes s inner join products p on p.sizeid = s.sizeid
inner join producttemplates pt on p.ptid = pt.ptid
where pt.ptid = @productid

union all
select 3 as Tag,
2 as Parent,
null as [tshirt!1!Name],
null as [variant!2!size],
null as [variant!2!productID!element],
null as [variant!2!dimensions!element],
pDim.ShirtLength as [dimensions!3!length!element]

from ProductDimensions pDim
INNER JOIN products p on p.pid = pDim.pid
where p.ptid = @productid

for xml explicit

ANY HELP WOULD BE MASSIVELY APPRECIATED!