0

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?

  • 1
    You lost me at add a new schema to the schema collection on an existing table - what tool are you using to do this? – Aaron Bertrand Oct 7 '14 at 20:55
  • Just a SQL Script – Sinaesthetic Oct 7 '14 at 22:06
1

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
  • I haven't tested this, but off the top of your head, have you any idea how performant this is? – Sinaesthetic Oct 7 '14 at 22:07
  • It's a bit limited as you can only extend the schema and I've only done with low volumes. I can rig up a volume test if you need it. Does it work for your scenario? Try with your volumes and let us know : ) – wBob Oct 7 '14 at 22:14
  • I think it'll work in one direction, at least. Unfortunately, for the rollback script, I might still be stuck with dropping the schema collection completely and recreating it with the old info since I can't remove a single schema from the collection. :/ – Sinaesthetic Oct 7 '14 at 22:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.