locked
cursor replace function RRS feed

  • Question

  • User647458646 posted

    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). 

    One is a &#163;1m block of <a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>"><a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a></a>"><a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>"><a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>

    The desired output should be : 

    <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.

    Tuesday, November 18, 2014 10:09 AM

Answers