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 cursor function and but it does not perform the correct output:
ALTER FUNCTION [dbo].[ReplaceTags2](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Name VARCHAR(MAX)
DECLARE CUR CURSOR FAST_FORWARD FOR
SELECT name
FROM [dbo].[database_tags]
Where UploadDate >= '2014-09-01'
and @XML LIKE '%' + Name + '%'
OPEN CUR
WHILE 1 = 1
BEGIN
FETCH cur INTO @name
--IF @Name IS NOT NULL
IF @@fetch_status <> 0
BREAK
BEGIN
SELECT @XML = REPLACE(@XML,
@Name,
'<a href="<a href="pagename.aspx?tag='+@Name+'">'+@Name+'</a>')
END
--FETCH NEXT FROM CUR INTO @Name
END
CLOSE CUR;
DEALLOCATE CUR;
RETURN @XML
END
I pass the following XML Input to the UDF:
<Body><p align="justify">One is a 1m block of AIREM 2006-1X 2A3, which has never appeared on SMO.<p></Body>
the function above, outputs the following (which is incorrect).
<Body><p align="justify">One is a 1m block of <a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>, which has never appeared on SMO.<p></Body>
I have attached an example of my dataset in the following link below, for further reference as to what my dataset types are. http://sqlfiddle.com/#!6/96cac8/2
Please advice further, where I may be possible going wrong. Thank you for your help and time.
Member
230 Points
736 Posts
cursor replace function
Nov 18, 2014 10:09 AM|missy786|LINK
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 cursor function and but it does not perform the correct output:
ALTER FUNCTION [dbo].[ReplaceTags2](@XML VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @Name VARCHAR(MAX) DECLARE CUR CURSOR FAST_FORWARD FOR SELECT name FROM [dbo].[database_tags] Where UploadDate >= '2014-09-01' and @XML LIKE '%' + Name + '%' OPEN CUR WHILE 1 = 1 BEGIN FETCH cur INTO @name --IF @Name IS NOT NULL IF @@fetch_status <> 0 BREAK BEGIN SELECT @XML = REPLACE(@XML, @Name, '<a href="<a href="pagename.aspx?tag='+@Name+'">'+@Name+'</a>') END --FETCH NEXT FROM CUR INTO @Name END CLOSE CUR; DEALLOCATE CUR; RETURN @XML ENDI pass the following XML Input to the UDF:
the function above, outputs the following (which is incorrect).
The desired output should be :
I have attached an example of my dataset in the following link below, for further reference as to what my dataset types are.
http://sqlfiddle.com/#!6/96cac8/2
Please advice further, where I may be possible going wrong.
Thank you for your help and time.
All-Star
16806 Points
2777 Posts
Re: cursor replace function
Nov 20, 2014 12:13 AM|Kevin Shen - MSFT|LINK
Hi missy786,
For your issue is more related with sql query .I suggest that you can post it to the forum below for a professional solution:
http://forums.asp.net/1226.aspx/1?SQL+Server+SQL+Server+Express+and+SQL+Compact+Edition
Best Regards,
Kevin Shen.