How to replace special character in SQL 2005

USE [EXPORTHpixPROD]
GO
/****** Object:  StoredProcedure [dbo].[sp_CME_Parse_XML_Test]    Script Date: 07/20/2009 07:35:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_CME_Parse_XML_Test]
 
AS
 
DECLARE @strXML varchar(max)
DECLARE @XMLID int
DECLARE @myXMLID int
DECLARE @TestId as int
DECLARE @getData CURSOR
 
 
SET @getData = CURSOR FOR 
SELECT XMLID, xmlDATA 
FROM CME_Test_Results_XML
WHERE Processed = 0
 
DECLARE @intPointer int
 
OPEN @getData
FETCH NEXT
FROM @getData INTO @myXMLID, @strXML
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
exec sp_xml_preparedocument @intPointer output, @strXML
	
INSERT INTO CME_Parsed_Test_XML
 
 
SELECT      TestDate,
            Pass_Fail,
			TestScore,
			EventID,
			LicenseNumber,
			RegistrationTime,
			SLUserID	
FROM      OPENXML(@intPointer, '/Test', 2) 
WITH (
                        [TestDate][datetime] './TestDate',
                        [Pass_Fail][nvarchar](10) './Pass_Fail',
						[TestScore][nvarchar](255) './TestScore',
						[EventID][bigint] './EventID',
						[LicenseNumber][nvarchar](50) './LicenseNumber',
						[RegistrationTime][datetime] './RegistraionTime',	
						[SLUserID][bigint] './SLUserID'
	)
 
SELECT @TestId = SCOPE_IDENTITY()
 
 
INSERT INTO CME_Parsed_Test_Results_XML
 
 
SELECT  TestID = @TestId,
        QuestionID,
        QuestionText,
        COALESCE(AnswerID, -1) as AnswerID,
        replace(AnswerText,char(39), char(39)+char(39)),
		''
FROM      OPENXML(@intPointer, '/Test/QuestionID/AnswerText', 2)
WITH (                   
            [AnswerText][nvarchar](4000) 'node()',             
            [QuestionID][int] '../@id',
            [QuestionText][nvarchar](4000) '../QuestionText',
            [AnswerID][int] './@id'
      ) WHERE AnswerID is not NULL
 
 
 
DECLARE @addtlText as nvarchar(4000)
DECLARE @addtlID as int
SELECT  @addtlID = QuestionID,
        @addtlText = AnswerText
FROM      OPENXML(@intPointer, '/Test/QuestionID/AnswerText', 2)
WITH (                   
            [AnswerText][nvarchar](4000) 'node()',             
            [QuestionID][int] '../@id',
            [QuestionText][nvarchar](4000) '../QuestionText',
            [AnswerID][int] './@id'
      ) WHERE AnswerID is NULL
 
IF (@addtlID > 0)
	BEGIN
		UPDATE CME_Parsed_Test_Results_XML SET AnswerText2 = replace(@addtlText,char(39), char(39)+char(39)) WHERE QuestionID = @addtlID AND TestID = @TestId
	END
 
 
 
exec sp_xml_removedocument @intPointer
 
 
UPDATE CME_Test_Results_XML SET Processed = 1 WHERE XMLID = @myXMLID
 
 
FETCH NEXT
FROM @getData INTO @myXMLID, @strXML
END
CLOSE @getData
DEALLOCATE @getData

Select allOpen in new window