SQL Server 2008 R2 – Efficiently Applying New Schema to Existing Data

sql-server-2008-r2xml

I'm needing to add a new schema to the schema collection on an existing table. Right now, I simply unbind the old schema collection from the table column, drop it, create the new schema, and rebind it to the table column.

The problem is, I need to preserve all of the data already in the table. What looks like is happening, is that when I apply the new schema, it goes through all of the data and revalidates everything which takes a really long time. Is there a better way to do this?

What I've tried so far is to rename the existing the table (e.g. Mytable_bak), then recreate the same table but with the new schema collection attached, select all of the data out of the _bak table into the newly created table, and then drop the _bak table. This is how I've done in the past and it seems to be a bit faster (not sure why). The problem with this is that it's really just a big pain. The table is actually published for replication which causes it to throw errors. I guess maybe if there was a way to temporarily disable replication for the table, make the migration, then re-replication, then that might work. But this is kind of out of my current knowledge.

So I'm wondering if there's a better way to achieve all of this, or maybe just a solution to my current approach?

Best Answer

You can do some limited adjustments to the schema with ALTER XML SCHEMA COLLECTION. Here's a simple example:

USE tempdb
GO

IF OBJECT_ID('dbo.users') IS NOT NULL DROP TABLE dbo.users
GO
IF EXISTS ( SELECT * FROM sys.xml_schema_collections WHERE name = N'xsd_test')
DROP XML SCHEMA COLLECTION xsd_test
GO

-- Create a simple schema
CREATE XML SCHEMA COLLECTION xsd_test AS '<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="users">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded" name="user">
                    <xs:complexType>
                        <xs:attribute name="name" type="xs:string" use="required" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>'
GO

-- Create a table with XML bound to that schema
CREATE TABLE dbo.users ( userId INT IDENTITY PRIMARY KEY, userXML XML(xsd_test) )
GO

-- Populate the table
INSERT INTO dbo.users ( userXML )
VALUES
    ('<users><user name="Sinaesthetic"/></users>' ),
    ('<users><user name="wBob"/></users>' )
GO

-- Alter the schema
IF EXISTS ( SELECT * FROM sys.xml_schema_collections WHERE name = N'xsd_test')
ALTER XML SCHEMA COLLECTION xsd_test
ADD '<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="skills">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded" name="skill">
                    <xs:complexType>
                        <xs:attribute name="name" type="xs:string" use="required" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>'
GO

UPDATE dbo.users
SET userXML.modify('insert <skills><skill name="someSkill"></skill></skills> after (users)[1]')
WHERE userId = 1
Related Question