XML Schema Support in SQL Server.

SQL Server has increased it support for XML version after version .
From SQL Server 2005 onwards , we can declare xml schema as schema collection and have SQL Server to validate the incoming XML to make sure it conforms to the schema declarations .

Here is an example.

The following creates xml schema called ( movie_collection) . 

1>
2>  CREATE XML SCHEMA COLLECTION dbo.movie_collection AS '
3~   
4~  
5~ ';
6>  GO

Let us create a table called movies with xml as one of its datatype and to be validated against the xml schema collection created above.

1> CREATE TABLE Movies (
2>      i INT identity(1, 1)
3>      ,dvd XML(movie_collection)
4>      );
5>
6> GO

As the following XML is valid according to the schema , the insert succeeds .

1>  insert into Movies ( dvd) values (
2>  '
3~ 
4~  Mind your language
5~  '
6>  )
7>  ;
8>  GO

(1 rows affected)


1> select * from movies;
2> go
i dvd
- ---
1
 Mind your language
 

(1 rows affected)


Now , let us  try insert someother xml which does not conform to the schema .

1>  insert into Movies ( dvd) values (
2>  '
3~ 
4~  Tamil Padam
5~  '
6>  )
7>  ;
8>  GO
Msg 6913, Level 16, State 1, Server ITACS-225, Line 1
XML Validation: Declaration not found for element 'dvdame'. Location: /*:dvdame[1]


With the above mentioned approach , we can easily manage XML in SQL Server.
Comments welcome.