Re: Changing schemas already in an xml schema collection

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Neal,

Yes, you're right. In SQL Server 2005 there is no replace or delete a schema
from a schema collection. It's popular as a "sought-after for next release"
feature. You may be able to add definitions to an existing schema in a
collection (using ALTER...ADD) as long as they don't redefine existing
types.

Your process seems right, and I'd drop/rebuild the indexes when I dropped
the schema collection from the column/and added the schema collection back
to the column. The documents in the XML column are re-validated when you
alter the table to add the schema collection back, this may take some time,
depending on how many rows you have.

Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb

"Neal Walters" <NealWalters@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ACA61EC4-D430-4E81-B4ED-021CD2A8ABB5@xxxxxxxxxxxxxxxx
One more question for the day.

I tried to alter the schema collection but only see the "ADD" keyword,
which
then gives error about duplicate of same namespace. Seems like their
needs
to be a "REPLACE" keyword instead of the ADD?

I also noticed that when you want to change a schema in the collection,
you
have apparently have to do the following:
1) alter table/alter column to remove schema collection from column
2) drop schema collection
3) create schema collection
4) alter table/atler column to re-add schema collection

Any dangers in this procedure? Do you mess up the indexes or any
performance problems when you want to change a schema in a collection?

I guess it would be obvious that you could not remove an XML element if
that
element is actually stored in the database, but i could see quite often
adding new elements to the schema.

Thanks in advance,
Neal Walters
http://Biztalk-Training.com



.