FOR XML…XMLSCHEMA and schema validation

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 ( 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

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

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.

2 thoughts on “FOR XML…XMLSCHEMA and schema validation

  1. You can rewrite the first query in a more performing way as:

    declare @x xml
    select @x = (select * from authors where 1=0 for xml auto, type, xmlschema(‘urn:authors’))
    create xml schema collection authorsxsd
    as @x

  2. Thanks Eugene. Dan Sullivan also wrote me with this correction:

    This is a better query

    declare @x xml
    select @x =
    (select * from SomeTable for xml auto,
    type, xmlschema(‘urn:stuff’))
    declare namespace xs =

    If the table contains XML Serializable UDTs or xml columns that are schema
    validated, then all the schemas will be needed for the schema collection.
    For the authors table it just happens that there is only one schema, but you
    really don’t know that in advance.

    I’m guessing that Eugene’s method will also work for multiple schema. Although I did remind Dan that UDTs do not work with "FOR XML" out of the box. You need to SELECT CONVERT(xml, yourudt_instance).

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.