Search code examples
sqlsql-server-2005t-sqlsql-server-2000for-xml

SQL Server 2000 XML PATH('') error

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?

Solution

  • Concatenation in SQL Server 2000:

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

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

select @s;