SQL Server 2000 XML PATH('') error

Question

SELECT
**FIELDS**
AS [text()] --Stops the XMLPATH line rendering output as XML
FROM #temp
WHERE  **CONDITIONS**
FOR XML PATH('') 

This doesn't work in SQL server 2000 (unsupported). I have tried using FOR XML RAW but it returns loads of useless information. i.e:

 <row text x0028 x0029="blah, blah"> <row text x0028 x0029="blah"> 

The above code currently returns a concatenated string(made up of multiple columns of varying types) from every single row in the table.

How can I achieve this in SQL Server 2000?

Answer 1

Concatenation in SQL Server 2000:

declare @s varchar(8000);
set @s = '';

select @s = @s + field1 + field2 + field3
from #temp
where ...
order by ...;

select @s;