When reading the CTPNotes file from the new Feb CTP build I stumbled across the fact that the XML schema that contains SQL data types (http://schemas.microsoft.com/sqlserver/2004/sqltypes) is now built-in to the server. Although this may not mean much to most people, it gave me the chance to try something that Dan Sullivan thought up for the first rev of our SQL Server 2005 class. It works now.

One of the enhancements to SELECT…FOR XML is ability to request that the XML it produces be prepended by an XML schema that describes it. A recent change allows you to choose the namespace for that schema. Dan's idea was to add the prepended schema to create a schema collection. After storing the FOR XML outside in an XML schema-valid column, you could make updates to the column that would be validated by the schema. You'd set this up like this:

declare @x xml
select @x = (select * from authors for xml auto, type, xmlschema('urn:authors')).query('*[1]')
create xml schema collection authorsxsd
as @x
go

create table authorsxml (
 id int primary key identity, — primary key required if XML index needed
 authors xml(authorsxsd))
go

declare @x xml(authorsxsd)
set @x = (select * from authors for xml auto, type, xmlschema('urn:authors')).query('/*[position()>1]')
insert authorsxml values(@x)

The XML Schema produced in the first step will now validate any information entered or updated in the table.

Why the Feb CTP change makes this work is FOR XML….XMLSCHEMA uses the SQL data types schema that's now built in. In previous betas, you could use this schema (error: not built in) or add the schema manually (error: it is built in [but it wasn't]). Thanks SQL Server 2005 XML folks, for this.