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
Showing posts with label keyword. Show all posts
Showing posts with label keyword. Show all posts
Sunday, March 11, 2012
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.com
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@.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
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
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@.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
Subscribe to:
Posts (Atom)