FOR XML and subselects

I thought SQL 2005 allowed this but perhaps i misread something.
SELECT column1, column2, ...
, (SELECT column1, column2, ... FROM table2 WHERE table2.column1 = table1.column1 FOR XML AUTO, TYPE) data
FROM Table1
That would return all the columns of table1 and a column of type xml for
the data in table2.
I have this:
select productid ,
(
select rtrim(xrefnum) xrefnum, qty from productxref where productid = '13737'
FOR XML path ('xref'),type
) xref
from product p
where productid = '13737'
which always fails with this:
Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'XML'.
Is this possible?
danHello Dan,
Yes, it's possible.
Try this:
use northwind
go
select ProductID,
(select rtrim(productName) name
,supplierID
,categoryID
from dbo.products pi
where productName = 'Chai'
for xml path('pd'),type) xref
from dbo.products p
where productName = 'Chai'
go
If it works, I can't explain your problem unless your DBCOMPAT isn't 90.
If it doesn't work, post back what error message you get along with the output
of SELECT @.@.VERSION.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/