Did you know that you can define an XML schema for every XML column or variable that you want? Defining XML schemas is not only good practice, because it validates the data as soon as you store it in the column or variable, but it also improves XML query performance.

Defining a schema

First off, use the CREATE XML SCHEMA COLLECTION statement to create the schema as a database object.

CREATE XML SCHEMA COLLECTION dbo.PersonSchema AS '
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element name="Person">
        <xsd:complexType >
            <xsd:sequence>
                <xsd:element name="Title" type="xsd:string" minOccurs="0" />
                <xsd:element name="FirstName" type="xsd:string" />
                <xsd:element name="LastName" type="xsd:string" />
                <xsd:element name="ShoeSize" type="xsd:int" minOccurs="0" />
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>';

The schema is created as a persistent database object.

Declaring a variable or a table column

You can use the DECLARE or CREATE TABLE syntax as with any xml variable or table column, except you add the schema name after the xml datatype declaration, like this:

DECLARE @persons xml(dbo.PersonSchema);

CREATE TABLE Sales.BranchOffices (
    BranchOfficeID    int IDENTITY(1, 1) NOT NULL,
    BranchOfficeName  varchar(100) NOT NULL,
    SalesPeople       xml(dbo.PersonSchema) NOT NULL,
    CONSTRAINT PK_BranchOffices PRIMARY KEY CLUSTERED (BranchOfficeID)
);

After that, you can set your variables or columns to XML strings, but from now on, SQL Server will validate your XML input against the XML schema you’ve provided.

INSERT INTO dbo.BranchOffices (BranchOfficeName, SalesPeople)
SELECT 'London', '
    <Person>
        <FirstName>A</FirstName>
        <LastName>Smith</LastName>
        <ShoeSize>9</ShoeSize>
    </Person>
    <Person>
        <Title>Mr</Title>
        <FirstName>B</FirstName>
        <LastName>Smythe</LastName>
    </Person>';

As usual, you can (and probably should) also add XML indexes to XML columns you frequently access:

CREATE PRIMARY XML INDEX BranchOffices_xmlix_SalesPeople
    ON dbo.BranchOffices (SalesPeople);

Dropping schemas

To drop an xml schema, simply use the DROP XML SCHEMA COLLECTION statement. Note, however, that you can’t do this if the schema is still bound to an existing database object such as a table column.

DROP XML SCHEMA COLLECTION dbo.PersonSchema;