A variable can only hold one result so all this:

DECLARE @N VARCHAR(MAX)

SELECT @N = [Name] FROM [dbo].[database_tags]

Is returning is the Name from the first row it encounters. So you need to do something like this (not tested at all just off the top of my head)

SELECT @XML = REPLACE(@XML,

Name,

'<a href="<a href="pagename.aspx?tag='+Name+'">'+Name+'</a>')

FROM

dbo.database_tags