Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 760
JustAnotherDeveloper Avatar asked Dec 28 '22 10:12

JustAnotherDeveloper


2 Answers

Concatenation in SQL Server 2000:

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

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

select @s;
like image 50
GSerg Avatar answered Dec 30 '22 10:12

GSerg


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

like image 26
Adam Wenger Avatar answered Dec 30 '22 11:12

Adam Wenger