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)
Related posts:
- SQLXML : How to Create an XML Schema …
- SQLXML : How to List Schema Elements and Attributes …
- SQLXML : How to Check if an XML Schema Already Exists …
- View or function ’sys.dm_exec_sessions’ has more column names … …
- Valid SQLXML XSD Data Types, and Sample SQL Server XML Schemas …
- SQL Server XML Red Gate Ebook, XQuery Labs …
Filed under:
SQLXML, T-SQL Tips and Tricks
I was looking for suggestions on how to transform the data to comply with a schema change, so I laughed when I saw your lines 26-28. This article provides an option that I think will work for me:
http://www.extremeexperts.com/SQL/Yukon/ChangingXMLSchema.aspx