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.
