I have a temporary solution that I have worked out, still looking for a better solution. I build a SQL function and added that to my query to "remove all the tags" Took an example from (http://www.thescripts.com/forum/thread591852.html) and modified to keep the <BR> and </p> and remove the rest. I am sure I will have to keep modifing onward.
Modified query to be:
select DisplayName,
(Select PropertyValue from userProfile where UserID = Users.UserID and PropertyDefinitionID = 65) as [City],
(Select PropertyValue from userProfile where UserID = Users.UserID and PropertyDefinitionID = 66) as [Province],
(Select PropertyValue from userProfile where UserID = Users.UserID and PropertyDefinitionID = 67) as [Country],
dbo.RemoveHTMLTags (Select PropertyValue from userProfile where UserID = Users.UserID and PropertyDefinitionID = 74)) as [Biography]
from users
inner join UserRoles on users.userid = userroles.userid
where userroles.RoleID = 7
order by LastName
Function Added to DNN Db
alter function dbo.RemoveHTMLTags
(
@tmpstr as varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
declare @i int
set @i = patindex('%<br>%', @tmpstr)
while @i > 0
begin
set @tmpstr = stuff(@tmpstr, @i, charindex('<br>', @tmpstr, @i) - @i + 10, 'wsxcderfv')
set @i = patindex('%<br>%', @tmpstr)
end
set @i = patindex('%</P>%', @tmpstr)
while @i > 0
begin
set @tmpstr = stuff(@tmpstr, @i, charindex('</P>', @tmpstr, @i) - @i + 10, 'wsxcderfv')
set @i = patindex('%</P>%', @tmpstr)
end
set @i = patindex('%&nbsp;%', @tmpstr)
while @i > 0
begin
set @tmpstr = stuff(@tmpstr, @i, charindex('&nbsp;', @tmpstr, @i) - @i + 10, ' ')
set @i = patindex('%&nbsp;%', @tmpstr)
end
set @i = patindex('%<%>%', @tmpstr)
while @i > 0
begin
set @tmpstr = stuff(@tmpstr, @i, charindex('>', @tmpstr, @i) - @i + 4, '')
set @i = patindex('%<%>%', @tmpstr)
end
set @i = patindex('%wsxcderfv%', @tmpstr)
while @i > 0
begin
set @tmpstr = stuff(@tmpstr, @i, charindex('wsxcderfv', @tmpstr, @i) - @i + 9, '<br>')
set @i = patindex('%wsxcderfv%', @tmpstr)
end
return @tmpstr
END