Way to convert a varchar type column is to xml type with preservation of already existing data in SQL Server

Introduction

Let us say we have a table with one Email column of Type varchar (this is just for example sake as in real time there will not be any employee table with only one email column). The column size is being specified as a length of 100 (i.e. varchar(100)) and with huge data populated.The polulated data contains some special characters like (&,< etc.) which does not created any problem since the column type is varchar.

Now a recent change in the requirement has been proposed where in we need to change the Email column type from varchar to xml with all the exisitng records being preserve but converted into a specific xml format.The xml format is given as under

<EmployeeEmails>
            <Email>TestMail@testmail.com</Email>           
</EmployeeEmails>

We need to generate a script file for this that needs to be pass on to the QA guys and they will run it in their QA environment for further testing.If the script holds a valid one, they that will be executed in the production environment also.One more important thing to tell,they(QA) can run the script 'n'- number of times and the script should not give any problem.

Solution 1 (Runs only for initial time)

/***********************************************************************
 Purpose:
 To change the 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 Email_Temp of type XML into the Employee Table

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

    N.B.~ The format will be        
        <EmployeeEmails>
					<Email>TestMail@testmail.com</Email>           
				</EmployeeEmails>

3. Drop the exisitng Email

4. Rename Email_Temp column to Email 
***********************************************************************/

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

--===== Copy the Email contents from Email to Email_Temp column 
    --  after proper conversion to xml type
UPDATE [dbo].[Employee]
SET Email_Temp = CAST('<EmployeeEmails><Email>' + REPLACE(Email, '&', '&') + '</Email></EmployeeEmails>' AS XML)
GO

--===== Drop the exisitng partner_email
ALTER TABLE [dbo].[Employee]
DROP COLUMN Email 
GO

--===== Rename Email_Temp column to Email 
EXEC SP_RENAME 'Employee.Email_Temp','Email','COLUMN'
GO

This script works fine for the first time we ran. Now if we ran it further we are 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.

Reason:

Let us see what the script says

1. If the column Email_Temp does not exist in the Employee table, add Email_Temp as an XML column
2. Update the Employee table. Change the value of Email_Temp to be an XML-friendly string version of Email. Then convert it to XML. Use REPLACE to convert all & to the encoded '&'amp
3. Drop the column Email from Employee
4. Rename the column Email_Temp to Email

All of this works the first time. When we run the script a second time, SQL Server gets to Step 2 and executes the ALTER TABLE statement again. This is because the column Email_Temp does not exist as it was renamed in the first run to Email.

Now in step 3, we are attempting to update the "Email" column again. The problem is that this column is now an XML data type and the use of REPLACE is illegal. So SQL Server throws an error.

Solution 2 (Runs always)

Update the data in the "Email" column to the correct format and then convert the column to an XML data type. This can be achieved in one SQL batch statement but we need to execute it as dynamic SQL so that SQL Server will only try to execute the REPLACE statement when the "Email" column is a varchar data type.But before that we need to increase the length of the "Email" column.

 /**************************************************************************************************************
 Purpose:
 To change the Email column from Varchar Type To Xml Type and convert the 
 existing records from varchar to xml types.
 
 Programmers Notes:  
 1. Check if the "Email" column is present in the Employee table and if so whether it is of XML data type
 
 2. If Step 1 is invalid, proceed to step 3 else stop
 
 3. Increase the existing "Email" column length from 100 characters to maximun(8000 characters)
 
 4. Update the data in the "Email" column to the correct format
 
		N.B.~ The format will be		
			<EmployeeEmails>
            <Email>TestMail@testmail.com</Email>           
	    </EmployeeEmails>
		
5. Convert the "Email" column from VARCHAR to XML data type
****************************************************************************************************************/
IF NOT EXISTS 
(
    SELECT * 
    FROM INFORMATION_SCHEMA.columns 
    WHERE table_name = 'Employee'
    AND   column_name = 'Email'
    AND   data_type = 'xml'
)
BEGIN
  DECLARE @sqlCmd NVARCHAR(4000)
  SET @sqlCmd = N'
  
  ALTER TABLE [dbo].[Employee] ALTER Column Email VARCHAR(MAX) 
  
  UPDATE [dbo].[Employee]
  SET Email = ''<EmployeeEmails><Email>'' 
  + REPLACE(Email, ''&'', ''&'') 
  + ''</Email></EmployeeEmails>'';
  
  ALTER TABLE [dbo].[Employee] ALTER Column Email XML
  '
  EXEC (@sqlCmd) 
END

GO

Conclusion

So we came to know as how by using dynamic scrit we can change the varchar datatype to xml datatype with existing data being preserved.Hope this helps

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.