Initially I have a column (partner_email) of varchar.Now a recent change has come where it needs to be changed to be changed to the XML type but the previous records needs to be reserve into the new column. I have applied the below algorithm to accomplish the work

/***********************************************************************
 Purpose:
 To change the partner_email column from Varchar Type To Xml Type and convert the 
 existing records from varchar to xml types.

 Programmers Notes:  
 1. Create a new Column by the name partner_email_temp of type XML into the Partner Table

 2. Copy the Email contents from partner_email to partner_email_temp column after proper conversion

    N.B.~ The format will be        
        <PartnerEmails>
            <Email>[email protected]</Email>
            <Email /> 
            <Email />
        </PartnerEmails>

3. Drop the exisitng partner_email

4. Rename partner_email_temp column to partner_email 
***********************************************************************/

USE [Test]
GO

--===== Create a partner_email_temp column of type xml into the Partner table
IF NOT EXISTS 
(
    SELECT * 
    FROM INFORMATION_SCHEMA.columns 
    WHERE table_name = 'Partner'
    AND   column_name = 'partner_email_temp'
)
BEGIN
    ALTER TABLE [dbo].[Partner] ADD partner_email_temp XML NULL
END
GO

--===== Copy the Email contents from partner_email to partner_email_temp column 
    --  after proper conversion to xml type
UPDATE [dbo].[Partner]
SET partner_email_temp = CAST('<PartnerEmails><Email>' + REPLACE(partner_email, '&', '&amp;') + '</Email><Email></Email><Email></Email></PartnerEmails>' AS XML)

GO

--===== Drop the exisitng partner_email
ALTER TABLE [dbo].[Partner]
DROP COLUMN partner_email 

GO
--===== Rename partner_email_temp column to partner_email 
Exec sp_RENAME 'Partner.partner_email_temp','partner_email','COLUMN'

GO

I works fine for the first time I ran. Now if I ran it for the next time, it am getting an error

Msg 8116, Level 16, State 1, Line 4 Argument data type xml is invalid for argument 1 of replace function. Caution: Changing any part of an object name could break scripts and stored procedures.

The intention is that, if the partner_email column is varchar, the script will change it to xml type and will convert all the data in xml format . If I ran it second time, it should ignore the statement.

How to achieve this?

I am trying in a different way

DECLARE @columnDataType VARCHAR(50)

SELECT @columnDataType = DATA_TYPE
FROM INFORMATION_SCHEMA.columns 
WHERE table_name = 'Partner'
AND   column_name = 'partner_email'

print @columnDataType

IF (@columnDataType = 'varchar')

BEGIN

        --===== Create a partner_email_temp column of type xml into the Partner table
        IF NOT EXISTS 
        (
            SELECT * 
            FROM INFORMATION_SCHEMA.columns 
            WHERE table_name = 'Partner'
            AND   column_name = 'partner_email_temp'
        )
        BEGIN
            ALTER TABLE [dbo].[Partner] ADD partner_email_temp XML NULL 

            --===== Copy the Email contents from partner_email to partner_email_temp column 
                --  after proper conversion to xml type
            UPDATE [dbo].[Partner]
            SET partner_email_temp = CAST('<PartnerEmails><Email>' + REPLACE(partner_email, '&', '&amp;') + '</Email><Email></Email><Email></Email></PartnerEmails>' AS XML)



            --===== Drop the exisitng partner_email
            ALTER TABLE [dbo].[Partner]
            DROP COLUMN partner_email 


            --===== Rename partner_email_temp column to partner_email 
            EXEC sp_RENAME 'Partner.partner_email_temp','partner_email','COLUMN'

          END   
END

but getting error

Msg 207, Level 16, State 1, Line 29 Invalid column name 'partner_email_temp'.

Help needed