How to generate a script for changing a column of varchar to xml type with data being converted?
- by user1323981
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, '&', '&') + '</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, '&', '&') + '</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
