XML Schema Collections are an important feature in SQL Server when it comes to restricting and validating the content of an XML document. In this article, we will explore how XML Schema Collections can be used to validate XML documents in T-SQL.

What are XML Schema Collections?

XML Schema Collections, also known as XSD (XML Schema Definition Language), are used to describe and validate the structure and values of XML documents. They provide a way to define rules and constraints that an XML document must adhere to.

Example XML Document

Let’s consider an example XML document that we want to validate:

    Jacob
    V
    Sebastian

Validation Rules

In order to validate the XML document, we need to define some validation rules:

• The root element should be named ‘Employee’
• The root element should have three child elements, named ‘FirstName’, ‘MiddleName’, and ‘LastName’
• The child elements should appear exactly in the order given above

Creating the XML Schema Collection

To perform the above validations, we need to create an XML Schema Collection. Here is an example of how to create it:

CREATE XML SCHEMA COLLECTION EmployeeSchema
    AS '
    
        
            
                
                
                
            
        
    
'

Validating an XML Variable

Once we have created the XML Schema Collection, we can use it to validate XML documents. Here is an example of how to validate an XML variable:

DECLARE @x XML(EmployeeSchema)
SELECT @x = '
    Jacob
    V
    Sebastian
'

Note that the validation will only succeed if all the validation rules defined in the XML Schema Collection are met. For example, if we try to validate an XML document that is missing the ‘MiddleName’ element, the validation will fail.

DECLARE @x XML(EmployeeSchema)
SELECT @x = '
    Jacob
    Sebastian
'

Conclusion

XML Schema Collections provide a powerful way to validate XML documents in SQL Server. By defining validation rules using XSD, we can ensure that the XML documents stored in XML columns or variables adhere to a pre-defined structure and values.