Using OPENROWSET in SQL 2005 for XML Schema Definition

In SQL 2005 you can associate a schema with a column to validate the domain values
that are inserted into a complex object represented by an XML document.

This is excellent because it means that you can validate xml that is inserted into
the new XML data type columns that are available.

However, one of my students didn't like the fact that you have to cut and paste the
Schema into a script used to create the collection, and I couldn't agree with him
more that this was not a very flexible approach if you were receiving the schema
from a third party. Ok so you can use BizTalk or other software to give a hand but
it would be better not to have to rely on anything else other than TSQL, unless you decide
this is appropriate in the given case.

I remebered you could use a linked server in 2000 and showed him how to do it using
this but then I had a look at the new features of 2005 and found that the OPENROWSET
Function has been enhanced for and will meet just this kind of requirement now as shown:-

This is the normal way of creating an XML Schema collection

CREATE XML SCHEMA COLLECTION myTestSchemaCollection AS 'SomeSchemaLayout’

However, for flexibility we can do it like this

DECLARE @@Schema_collection XML
SELECT @@Schema_collection = Schema_Name
FROM OPENROWSET ( BULK 'C:\SomeSchemaLayout.TXT',SINGLE_CLOB)
AS Schema_File(Schema_Name)

Which can then be used as follows:-

EXECUTE ('CREATE XML SCHEMA COLLECTION myTestSchemaCollection AS '+ @@Schema_collection)