Typed XML data

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.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element name="Person">
        <xsd:complexType >
                <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" />

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,

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', '

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.


One thought on “Typed XML data

Let me hear your thoughts!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.