capturing for xml path('br') result into variable

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)

capturing for xml path('br') result into variable

Author

Topic

rafeequddin_ahmed
Starting Member

23 Posts

Posted - 2008-07-15 : 08:05:20

HI,

I wrote the following query

select replace(name,'<name>','') from tbl_usercontent where Expirydate between getdate() and dateadd(wk,1,getdate()) and tbl_usercontent.contentId in(select ContentId from tbl_payment_shoper_cart where userId=1)for Xml path('br')

it is giving the following result.

 V - New Package - 1 sff one package What is Communication is i know

what i have to do is i have to set this as HTML body of email which i m sending from sql server

Declare @tableHTML nvarchar(max);
SET @tableHTML =
N'Dear ' +-- @Prefix + @FN + @LN +
N'Your "' + 'Courses ' + select replace(name,'<name>','') from tbl_content where Expirydate between getdate() and dateadd(wk,1,getdate()) and tbl_content.contentId in(select ContentId from tbl_payment_shopping_cart where userId=1)for Xml path('br')
select @tableHTML

but i m gettig the following error

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'select'.

I think we can not cancatenate the result of for xml path with @tableHTML.
please help me out becuase I have to create dynamic text body for sending email from sql server.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-07-15 : 08:17:08

How about this?

SELECT @tableHTML =
N'<P><P>Dear <b>' +-- @Prefix + @FN + @LN +
N'</b>Your "<b>' + 'Courses ' + replace(name,'<name>','') from tbl_content where Expirydate between getdate() and dateadd(wk,1,getdate()) and tbl_content.contentId in(select ContentId from tbl_payment_shopping_cart where userId=1)for Xml path('br')

select @tableHTML

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rafeequddin_ahmed
Starting Member

23 Posts

Posted - 2008-07-15 : 08:21:09

SELECT @tableHTML =
N'Dear ' +-- @Prefix + @FN + @LN +
N'Your "' + 'Courses ' + replace(name,'<name>','') from tbl_content where Expirydate between getdate() and dateadd(wk,1,getdate()) and tbl_content.contentId in(select ContentId from tbl_payment_shopping_cart where userId=1)for Xml path('br')

select @tableHTML

Msg 6819, Level 16, State 3, Line 2
The FOR XML clause is not allowed in a ASSIGNMENT statement.

IF i remove the for xml path('br') it is concatination only one record. when executing the select query with for xml path it returning three records.

Regards,

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-07-15 : 08:37:55

[code]SET @tableHTML =
N'Dear ' +-- @Prefix + @FN + @LN +
N'Your "' + 'Courses ' + (select replace(name,'<name>','') from tbl_content where Expirydate between getdate() and dateadd(wk,1,getdate()) and tbl_content.contentId in(select ContentId from tbl_payment_shopping_cart where userId=1)for Xml path('br'))

select @tableHTML[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rafeequddin_ahmed
Starting Member

23 Posts

Posted - 2008-07-15 : 09:38:11

Yep it is working thank a lot buddy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources