Bloggin in the UK RSS 2.0
 Friday, May 21, 2010
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.ProductModel
WHERE ProductModelID= 7 or ProductModelID=9
FOR XML PATH('ProductModelData')

The key is the ", TYPE" after the "FOR XML PATH(''), the example on MSDN is missing this key component.

Friday, May 21, 2010 6:08:13 PM (GMT Standard Time, UTC+00:00)  #    Comments [0] -

Name
E-mail
Home page

Comment (HTML not allowed)  

Enter the code shown (prevents robots):

Live Comment Preview
Archive
<September 2010>
SunMonTueWedThuFriSat
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2010
Charlie Barker
Sign In
Statistics
Total Posts: 172
This Year: 14
This Month: 1
This Week: 0
Comments: 77
Themes
Pick a theme:
All Content © 2010, Charlie Barker
DasBlog theme 'Business' created by Christoph De Baene (delarou)