CODEWITHSUNDEEP
sqlsql-server-2005t-sqlsql-server-2000for-xml
JustAnotherDeveloper
JustAnotherDeveloper

Reputation: 3199

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?

Upvotes: 4

Views: 7623

Answers (3)

Sohail xIN3N
Sohail xIN3N

Reputation: 3041

You've to define some text or character inside FOR XML PATH(' ') like, FOR XML PATH('abc'). You can also use FOR XML RAW('abc') for outputting raw XML.

Upvotes: 0

Adam Wenger
Adam Wenger

Reputation: 17560

The XML datatype isn't supported in 2000, it was introduced in 2005.

SQL 2000 did introduce the FOR XML, but it only supported RAW, AUTO, and EXPLICIT

Upvotes: 3

GSerg
GSerg

Reputation: 78183

Concatenation in SQL Server 2000:

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

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

select @s;

Upvotes: 7

Related Questions