Thursday 31 January 2008

Convert XML in SQL Server

This is seriously cool.

In my C# I have read an xml file into a byte[] and stored it within the database in a varbinary(max) column.

If I wish to see this file as Xml in the database, all i need to do is the following SQL.



SELECT CONVERT(Xml, Source)
FROM MyFiles
WHERE MyFileID=7


Now what is even cooler, is if wish to validate said file against an Xml Schema, i can do the following.



SELECT CONVERT(Xml(MySchema), Source)
FROM MyFiles
WHERE MyFileID=7


So this is a really nice way of validating data in the database against an xml schema where you might not necessarily be able to used an Xml Typed column (accepting multiple file types)

No comments: