set nocount on

declare @yourTable table ([id] int, attributeName varchar(10), attributeValue varchar(10), productID int)
insert into @yourTable
	select 1, 'test', '1', 200 union
	select 2, 'test2', 'house', 200 union
	select 3, 'test3', 'giraffe', 200 union
	select 4, 'test', '2', 400 union
	select 5, 'test2', 'apartment', 400 union
	select 6, 'test3', 'tiger', 400 

-- dbcc traceon(257)

SELECT Tag = 1, Parent = NULL,
	[attributes!1!productID] = d.productID,
	[attributes!1!test!element] = d.test,
	[attributes!1!test2!element] = d.test2,
	[attributes!1!test3!element] = d.test3
FROM (	select	productId,
		max(case attributeName when 'test' then attributeValue end) as 'test',
		max(case attributeName when 'test2' then attributeValue end) as 'test2',
		max(case attributeName when 'test3' then attributeValue end) as 'test3'
	from @yourTable
	group by productID) d
FOR XML EXPLICIT