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)

Share :
  • Digg
  • del.icio.us
  • Google
  • description
  • StumbleUpon
  • Technorati
  • TwitThis

Related posts:

  1. SQLXML : How to Create an XML Schema …
  2. SQLXML : How to List Schema Elements and Attributes …
  3. SQLXML : How to Check if an XML Schema Already Exists …
  4. View or function ’sys.dm_exec_sessions’ has more column names … …
  5. Valid SQLXML XSD Data Types, and Sample SQL Server XML Schemas …
  6. SQL Server XML Red Gate Ebook, XQuery Labs …