How to Migrate Non XML Column into XML Column
How to Migrate Non XML Column into XML Column
By : Kasim Wirama, MCITP, MCDBA
You might store xml data in text or varchar column type in SQL Server 2000 or previous version because it doesn’t support XML data type for storage. Now SQL Server 2005 and SQL Server 2008 support XML data type natively. It means that developer could declare XML variable, XML column, XML input/output parameters in stored procedure and user defined function and return value from user defined function; so converting xml data in text or varchar column type into xml column type is recommended because you can put high performance XML validation and very easy to query XML data with available XQuery methods.
Here is safe procedure to do conversion into XML column:
1. Add temporary column with varchar(max) or nvarchar(max) or text type
2. Set value from a source varchar column into step 1
3. Drop source varchar column
4. Create new column whose name is same as dropped column on step 3.
5. Update back column in step 4 from value of column in step 2.
6. Drop the temporary column.
Here is sample how to do this.
CREATE TABLE dbo.CustomerSalesRecord
(
CustomerId int primary key,
SalesRecord nvarchar(8000)
);
GO
INSERT INTO dbo.CustomerSalesRecord
SELECT c.customerId,
(
SELECT
FROM sales.salesorderheader as h
WHERE h.CustomerId = c.CustomerId
FOR XML AUTO, ROOT(‘Orders’)
) as SalesRecord
FROM Sales.Customer as c;
1. First step
ALTER TABLE dbo.CustomerSalesRecord
ADD SalesRecord_temp NVARCHAR(MAX);
2. Second step
UPDATE dbo.CustomerSalesRecord
SET SalesRecord_temp = SalesRecord;
3. Third step
ALTER TABLE dbo.CustomerSalesRecord
DROP COLUMN SalesRecord;
4. Fourth step
ALTER TABLE dbo.CustomerSalesRecord
ADD SalesRecord XML;
5. Fifth step
UPDATE dbo.CustomerSalesRecord
SET SalesRecord = CONVERT(XML, SalesRecord_temp);
6. Sixth step
ALTER TABLE dbo.CustomerSalesRecord
DROP COLUMN SalesRecord_temp;