November 18, 2014 at 3:54 am
I am trying to replace names found in 'xml' fieldname as hyperlinks, by matching with the names in database_tags.
I am using the following function below, however the UDF is only recognizing one name from the XML fieldname data, instead of all the names present in the XML data.
ALTER FUNCTION [dbo].[ReplaceTags](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @N VARCHAR(MAX)
SELECT @N = [Name] FROM [dbo].[database_tags]
WHERE @XML LIKE '%'+[Name]+'%'
AND UploadDate >= '2014-09-01'
IF @N IS NOT NULL
BEGIN
SELECT @XML = REPLACE(@XML,
@N,
'<a href="<a href="pagename.aspx?tag='+@N+'">'+@N+'</a>')
END
RETURN @XML
END
for example, if the XML input data is the following: It consists of: BANKP, BCJA, BCJAM, BFTH, BFTH, and EMPOP.
But the updated function is only recognizing two of names BFTH, BFTH,
as hyperlinks, from the database_tags table.
Is there a way to get the function to recognize more than one names as hyperlinks.
Thank you very much for your time and help.
November 18, 2014 at 10:27 am
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
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply