SQLXML : How to Alter Existing Column Schema (XSD)

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)

VN:F [1.9.22_1171]
Rating: 8.8/10 (5 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Free Database Schemas

Just came across a number of good free schemas for SQL Server Express:
http://www.microsoft.com/sql/editions/express/starterschemas.mspx

This is the list, which I am sure some of you will find useful:

  • Database Schema 1: Assets Maintenance
  • Database Schema 2: Contact Management
  • Database Schema 3: Customers and Orders
  • Database Schema 4: Document Management
  • Database Schema 5: e-Commerce
  • Database Schema 6: Help Desk
  • Database Schema 7: Issue Tracking Software
  • Database Schema 8: Retail Inventory Control
  • Database Schema 9: Not for Profits
  • Database Schema 10: Product Catalogs

These are all provided by Barry Williams, who has hundreds more in his site (www.DatabaseAnswers.org)! Will definitely check those out.

 

VN:F [1.9.22_1171]
Rating: 9.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`