USE pubs
GO

SELECT stor_id, stor_name, state,
 (SELECT discounttype, discount FROM discounts d
   WHERE d.stor_id = s.stor_id 
   FOR XML AUTO, ELEMENTS, TYPE)
FROM stores s
ORDER BY s.stor_id
FOR XML AUTO, ELEMENTS
GO
