2008 for xml path('') not working on 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

2008 for xml path('') not working on 2005

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2012-11-15 : 05:33:24

Hi ALl,

Got this statement running perfect on 2008, but doesn't seem to work on 2005. i think it is because "for xml path('')"
what changes do i have to make to get it to work?

SELECT @subject= + Subject, @ordernr=ordernr, @body= + (SELECT top 100 mailbody +'<BR>----- OTHER EMAIL -----<BR>' + Subject + '<BR>' as mailbody
FROM T_Customer_Mailbox
WHERE (userid = @userID) AND (datereply IS NULL) AND (ToUser <> 'system')
ORDER BY id for xml path('')), @from=useremail , @lang =lang
FROM T_Customer_Mailbox
where userid=@userid

thanks a lot

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-11-15 : 06:00:59

Hi,

may be 2005 is not cmpLevel 90 (2005)

check by this select

select compatibility_level from sys.databases where name=DBName

or with sp_dbcmptlevel

to be right it must be 90 lvl

mike13
Posting Yak Master

219 Posts

Posted - 2012-11-15 : 08:28:22

this it the result:
Valid values of the database compatibility level are 60, 65, 70, 80, or 90.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-15 : 08:46:50

You have to specify the database name to see its compatibility level:

sp_dbcmptlevel 'YourDatabaseNameHere'

'
This may not be the problem, but I didn't quite follow what the plus sign in the statements is doing

SELECT @subject= + Subject, @ordernr=ordernr,@body= + (SELECT top 100

mike13
Posting Yak Master

219 Posts

Posted - 2012-11-15 : 08:50:22

IT is adding all the email body from the user into 1 output parameter

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-15 : 09:17:00

I thought you would need to do "@subject +=" rather than "@subject = +". In any case, the += operator is supported only in SQL 2008 or later. If that indeed is the objective you should change it. The other alternatives are 1 and 3 in the example code below.

CREATE TABLE #tmp(a VARCHAR(32));
INSERT INTO #tmp VALUES ('abc'),('def');

DECLARE @x VARCHAR(32) = '',
	@y VARCHAR(32) = '',
	@z VARCHAR(32) = ''
	
-- 1
SELECT @x = @x + a FROM #tmp;
-- 2
SELECT @y += a FROM #tmp;
-- 3
SELECT @z = (SELECT a AS [text()] FROM #tmp FOR XML PATH(''));

SELECT @x,@y,@z;

DROP TABLE #tmp;

mike13
Posting Yak Master

219 Posts

Posted - 2012-11-15 : 09:45:33

suddenly it works, must have copy n paste wrong stuff to server

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources