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.

Originally published at https://zahirmohideen.blogspot.com/2013/10/xml-schema-support-in-sql-server.html