how to convert a table in database to xml to display as html

[nafi925]

hi all i need some help on my project'

i need to get xml string of a table from database to send mail's
am currently using a STORED PROCEDURE but am returning a nvarchar(max) value so the length is limited to 8000 so if it gets longer there is a problem. pls help me on this


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[sendinfo]
@bankid int,
@out NVARCHAR(MAX) output,
@mail varchar(50) output

as
DECLARE @tableHTML varchar(max)
begin

SET @tableHTML =N'<table border="1">' +
N'<tr><th>Credit_card Number</th><th>Name</th>' +
N'<th>Address</th><th>Last pay</th><th>amount_due</th></tr>' +

CAST ( ( SELECT td = [Credit_card Number], ' ',
td = Name, ' ',
td = Address, ' ',
td = [Last pay], ' ',
td = amount_due
FROM [Credit_mamagement_system].[dbo].[Info]where [bankID] = @bankid

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

SELECT @mail=[E-mail]
FROM [Credit_mamagement_system].[dbo].[Bank_Infomation]where [BankID]=@bankid
set @out =@tableHTML

END

[jojo41300000]

hi all i need some help on my project'

i need to get xml string of a table from database to send mail's
am currently using a STORED PROCEDURE but am returning a nvarchar(max) value so the length is limited to 8000 so if it gets longer there is a problem. pls help me on this


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[sendinfo]
@bankid int,
@out NVARCHAR(MAX) output,
@mail varchar(50) output

as
DECLARE @tableHTML varchar(max)
begin

SET @tableHTML =N'<table border="1">' +
N'<tr><th>Credit_card Number</th><th>Name</th>' +
N'<th>Address</th><th>Last pay</th><th>amount_due</th></tr>' +

CAST ( ( SELECT td = [Credit_card Number], ' ',
td = Name, ' ',
td = Address, ' ',
td = [Last pay], ' ',
td = amount_due
FROM [Credit_mamagement_system].[dbo].[Info]where [bankID] = @bankid

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

SELECT @mail=[E-mail]
FROM [Credit_mamagement_system].[dbo].[Bank_Infomation]where [BankID]=@bankid
set @out =@tableHTML

END

Hi,
Are you using SQL SERVER 2000? If it is the case, use text instead of varchar. I think It should solve the problem.

-Jason