File this under this how to know what you have fix before you fix an XML Schema Collection
When I’m introducing folks to XML Schema Collections in SQL Server 2005, conversation usually goes something like this:
Blah blah blah blah use to make sure that XML instances are valid according to an XML Schema blah blah blah and you create them using CREATE XML SCHEMA COLLECTION name AS blah blah blah. Naturally, you can delete such a schema collection using DROP XML SCHEMA COLLECTION but you have to make sure there are no dependent objects on that before doing so. Some more talk and the then I have to it. The part of the talk I like the second least.
“So there is an alter for XML schema collections and to nobody’s surprise that starts with ALTER XML SCHEMA COLLECTION followed by the qualified name of the schema collection and a verb. In SQL Server 2005 there is one supported verb: ADD.”
That means that the alter for an XML schema collection is really only good for doing either of things: adding additional elements to default namespace, or adding a schema for a new namespace. And that’s basically it. There’s really not a good way to fix a broken or out of date schema for an existing namespace. And that means – oh joy – that you need to alter all of the objects that use the schema to not do so before you go about fixing the schema. And the first step in that is, of course, getting an inventory of the objects you’ll need to touch.
Continued here, due to length.