Sometimes you may need to change schema definitions in existing columns.
The following shows an example of how you can do this:
1: -- drop the XML schema from existing column definitions
2: ALTER TABLE AD
3: ALTER COLUMN Title xml
4:
5: -- DROP IF EXISTS
6: IF EXISTS (SELECT name
7: FROM sys.xml_schema_collections
8: WHERE name='TitleSchema')
9: DROP XML SCHEMA COLLECTION TitleSchema
10: GO
11:
12: -- new definition
13: -- there can only be one Title element
14: CREATE XML SCHEMA COLLECTION TitleSchema AS
15: '<?xml version="1.0" encoding="utf-8"?>
16: <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
17: <xsd:element name="Title">
18: <xsd:complexType>
19: <xsd:attribute name="Type" type="xsd:string" />
20: <xsd:attribute name="Value" type="xsd:string" use="optional" />
21: </xsd:complexType>
22: </xsd:element>
23: </xsd:schema>'
24: GO
25:
26: -- IMPORTANT: before you add back the schema to the column
27: -- definition, make sure all values in your existing column
28: -- comply with the new schema definition
29: ALTER TABLE AD
30: ALTER COLUMN Title xml(TitleSchema)
1 Comment
Filed under:
SQLXML, T-SQL Tips and Tricks