To Produce this: ================<ProductModelData ProductModelID="7" ProductModelName="..." ProductIDs="product id list in the product model" > <ProductName>...</ProductName> <ProductName>...</ProductName> ...</ProductModelData>You need this:==============SELECT ProductModelID as "@ProductModelID", Name as "@ProductModelName", (SELECT ProductID as "data()" FROM SalesLT.Product WHERE SalesLT.Product.ProductModelID = SalesLT.ProductModel.ProductModelID FOR XML PATH ('')) as "@ProductIDs", (SELECT Name as "ProductName" FROM SalesLT.Product WHERE SalesLT.Product.ProductModelID = SalesLT.ProductModel.ProductModelID FOR XML PATH (''), TYPE) as "ProductNames"FROM SalesLT.ProductModelWHERE ProductModelID= 7 or ProductModelID=9FOR XML PATH('ProductModelData')The key is the ", TYPE" after the "FOR XML PATH(''), the example on MSDN is missing this key component.
Remember Me
Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.