Some days ago I started to read Inside SQL Server 2005: T-SQL Programming, a great book written by my good friends Itzik Ben-Gan (who given me a free copy, thanks again ), Dejan Sarka and Roger Wolter.
Yesterday coming back from work, I was reading about the XML Data Type paragraph, in particular using XML with Open Schema.
Dejan describes very clearly the possibility to associate an XSD Schema to an XML column.
In his code sample, he wants to store some contacts in a table. For domestic contacts, he needs to store an ID and a foreign spoken language. For foreign contacts, he needs to store the mother tongue and a flag that shows whether the contact speaks English or not.
The solution uses a single table with the following structure:

CREATE TABLE dbo.Contacts

(

    contactid       INT          NOT NULL,

    contactname     NVARCHAR(50) NOT NULL,

    domestic        BIT          NOT NULL,

    otherattributes XML          NOT NULL

);

Now I'd like to talk about domestic contacts only.
We can constrain the XML data with a schema collection. Values entered into the column will be validated against schemas in the collection and accepted only if they comply with any of the schemas in the collection.

Dejan suggests to let SQL Server generate the XML Schema for us using the new XMLSCHEMA option of the FOR XML clause.
This is the code that appears on the book:

CREATE TABLE dbo.Domestic

(

    ID              NVARCHAR(15),

    ForeignLanguage NVARCHAR(50)

);

GO

 

-- Store the schema in a Variable and Create the Collection

DECLARE @mySchema NVARCHAR(MAX);

 

SET @mySchema = N'';

 

SET @mySchema = @mySchema +

  (

    SELECT *

    FROM Domestic

    FOR XML AUTO, ELEMENTS, XMLSCHEMA('Domestic')

  );

 

-- Create Schema Collection

CREATE XML SCHEMA COLLECTION dbo.ContactOtherAttributes as @mySchema;

GO

 

-- Drop Table
DROP TABLE dbo.Domestic;

Now we need to alter the XML column from a well-formed state to a schema-validated one:

ALTER TABLE dbo.Contacts

    ALTER COLUMN otherattributes XML(dbo.ContactOtherAttributes);


Now insert some valid data:

INSERT INTO dbo.Contacts VALUES(1, N'Mike', 1, N'

<Domestic xmlns="Domestic">

   <ID>012345678901234</ID>

   <ForeignLanguage>Spanish</ForeignLanguage>

</Domestic>');

 

INSERT INTO dbo.Contacts VALUES(2, N'Richard', 1, N'

<Domestic xmlns="Domestic">

   <ID>012345678901234</ID>

   <ForeignLanguage>German</ForeignLanguage>

</Domestic>');
 
Next try insert some invalid data:

INSERT INTO dbo.Contacts VALUES(3, N'Kalen', 1, N'

<Domestic xmlns="Domestic">

   <ID>012345678901234</ID>

   <ForeignLanguage>Spanish</ForeignLanguage>

   <ForeignLanguage>German</ForeignLanguage>

</Domestic>');


Of course, we get the error:

Msg 6923, Level 16, State 1, Line 1

XML Validation: Unexpected element(s): Domestic:ForeignLanguage. Location: /*:Domestic[1]/*:ForeignLanguage[2]

This error was generated because I tried to insert a contact who speaks two foreign languages but the schema supports only one.
Dejan says that if we want to insert a contact like Kalen who speaks two foreign languages, we need to change the OtherAttributes XML column from schema-validated back to well-formed.

This sentence makes me think about a different solution that doesn't require to remove the schema binding with the XML column.
I writed to Dejan and he suggested me to post my solution somewhere on the web, so I decided to blogging it.

OK, let's start.
IMHO the Dejan's code has two weaknesses:

1) I can insert a contact wìthout the ID, ForeignLanguage or both attributes:

INSERT INTO dbo.Contacts VALUES(4, N'Steve', 1, N'

<Domestic xmlns="Domestic">

   <ForeignLanguage>German</ForeignLanguage>

</Domestic>');

 

INSERT INTO dbo.Contacts VALUES(5, N'Kalen', 1, N'

<Domestic xmlns="Domestic">

   <ID>012345678901234</ID>

</Domestic>');

 

INSERT INTO dbo.Contacts VALUES(6, N'Adam', 1, N'

<Domestic xmlns="Domestic">

</Domestic>');

2) If I remove the schema binding to the XML column, I can insert every well-formed XML fragment or document, with the risk to soil the data.

If we have a look to the schema generated by the SELECT...FOR XML query, we can see something interesting:

<xsd:schema targetNamespace="Domestic" xmlns:schema="Domestic" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">

  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />

  <xsd:element name="Domestic">

    <xsd:complexType>

      <xsd:sequence>

        <xsd:element name="ID" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">

              <xsd:maxLength value="15" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ForeignLanguage" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

      </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

</xsd:schema>

Both elements has the minOccurs="0" attribute because the Domestic columns were defined nullable.
minOccurs is an XML Representation Attribute that determines the minimum number of times that an element is required to appear in a content model. If the value of this attribute is 0, then the element is optional.
If we set this attribute to 1, we solved the first issue.

Well, if exists the minOccurs attribute, will exists the analogous maxOccurs
maxOccurs is an XML Representation Attribute that determines the maximum number of times that an element may appear in a content model. The value of this attribute may be a positive integer value or the term 'unbounded' to indicate that there is no maximum number of occurrences.
If we set this attribute to 2, we solved the last issue.

So I modified the schema in this way:

-- Remove the schema from the XML column

ALTER TABLE dbo.Contacts

    ALTER COLUMN otherattributes XML;

 

-- Drop the schema collection

DROP XML SCHEMA COLLECTION dbo.ContactOtherAttributes;

 

-- Add the modified schema to the schema collection

DECLARE @mySchema NVARCHAR(MAX);

 

SET @mySchema = N'<xsd:schema targetNamespace="Domestic" xmlns:schema="Domestic" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">

  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />

  <xsd:element name="Domestic">

    <xsd:complexType>

      <xsd:sequence>

        <xsd:element name="ID" minOccurs="1">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">

              <xsd:maxLength value="15" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ForeignLanguage" minOccurs="1" maxOccurs="2">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

      </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

</xsd:schema>';

 

CREATE XML SCHEMA COLLECTION dbo.ContactOtherAttributes as @mySchema;

GO

 

-- Alter the XML column from a well-formed state to a schema-validated one

ALTER TABLE dbo.Contacts

    ALTER COLUMN otherattributes XML(dbo.ContactOtherAttributes);

Now if I try to insert an invalid attribute, I will receive an error:

INSERT INTO dbo.Contacts VALUES(7, N'Ron', 1, N'

<Domestic xmlns="Domestic">

   <ForeignLanguage>German</ForeignLanguage>

</Domestic>');

 

/* Output:

 

Msg 6965, Level 16, State 1, Line 1

XML Validation: Invalid content. Expected element(s):Domestic:ID where element 'Domestic:ForeignLanguage' was specified. Location: /*:Domestic[1]/*:ForeignLanguage[1]

 

*/

INSERT INTO dbo.Contacts VALUES(8, N'Louis', 1, N'

<Domestic xmlns="Domestic">

   <ID>012345678901234</ID>

</Domestic>');

 

/* Output:

 

Msg 6908, Level 16, State 1, Line 1

XML Validation: Invalid content. Expected element(s): Domestic:ID. Location: /*:Domestic[1]

 

*/

INSERT INTO dbo.Contacts VALUES(9, N'Kent', 1, N'

<Domestic xmlns="Domestic">

</Domestic>');

 

/* Output:

 

Msg 6908, Level 16, State 1, Line 1

XML Validation: Invalid content. Expected element(s): Domestic:ID. Location: /*:Domestic[1]

 

*/

INSERT INTO dbo.Contacts VALUES(10, N'Andrew', 1, N'

<Domestic xmlns="Domestic">

   <ID>012345678901234</ID>

   <ForeignLanguage>Spanish</ForeignLanguage>

   <ForeignLanguage>German</ForeignLanguage>

   <ForeignLanguage>Italian</ForeignLanguage>

</Domestic>');

 

/* Output:

 

Msg 6923, Level 16, State 1, Line 1

XML Validation: Unexpected element(s): Domestic:ForeignLanguage. Location: /*:Domestic[1]/*:ForeignLanguage[3]

 

*/


While valid XML fragments will be accepted:

INSERT INTO dbo.Contacts VALUES(11, N'Aaron', 1, N'

<Domestic xmlns="Domestic">

   <ID>012345678901234</ID>

   <ForeignLanguage>Spanish</ForeignLanguage>

</Domestic>');

 

INSERT INTO dbo.Contacts VALUES(12, N'Tom', 1, N'

<Domestic xmlns="Domestic">

   <ID>012345678901234</ID>

   <ForeignLanguage>Spanish</ForeignLanguage>

   <ForeignLanguage>French</ForeignLanguage>

</Domestic>');


As you can see, using appropriately the minOccurs and maxOccurs XML Representation Attributes is possible to design an XML Schema with a great fine control on the the XML fragments permitted.

I want to thank Itzik, Dejan and Roger again for their invaluable work