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!
