Changing schemas already in an xml schema collection

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.comHi 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@.discussions.microsoft.com> wrote in message
news:ACA61EC4-D430-4E81-B4ED-021CD2A8ABB5@.microsoft.com...
> 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
>|||Bob, Thanks for all your responses!
Neal