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)