Sql-server – Why store XML Schema in SQL Server 2012 database

sql-server-2012xml

What a real world production environment example for storing and or using a XML schema in a SQL Server database?

I am working my way though the Microsoft 70-461 exam objectives. I have
completed the Microsoft walk through for creating and storing an XML
schema in the database at
https://technet.microsoft.com/en-us/library/ms176009(v=sql.110).aspx .

I have yet to work in a database environment where the application
will access a XML schema that is stored in the database. What is a
real world use case for creating and storing a XML schema in a
production SQL Server database?

Best Answer

XML Schemas aren't stored in SQL Server so that an application can access them. The reasons for storing XML Schema Collections are found in how they improve working with the XML data.

According to the MSDN page for XML Schema Collections:

  • SQL Server uses this type information to optimize data storage.

  • The query-processing engine also uses the schema for type checking and to optimize queries and data modification.

  • Also, SQL Server uses the associated XML schema collection, in the case of typed xml, to validate the XML instance. If the XML instance complies with the schema, the database allows the instance to be stored in the system with their type information. Otherwise, it rejects the instance.

And, according to the MSDN page for Compare Typed XML to Untyped XML:

An XML schema provides the following:

  • Validation constraints. Whenever a typed xml instance is assigned to or modified, SQL Server validates the instance.

  • Data type information. Schemas provide information about the types of attributes and elements in the xml data type instance. The type information provides more precise operational semantics to the values contained in the instance than is possible with untyped xml. For example, decimal arithmetic operations can be performed on a decimal value, but not on a string value. Because of this, typed XML storage can be made significantly more compact than untyped XML.

So the reasons to use an XML Schema Collection are:

  1. Increased functionality
  2. Optimized storage (this one is "iffy": I tested with type="xsd:int" and it took up more space than the untyped, but perhaps other types such as datetime and/or float optimize better)
  3. Increased performance
  4. Validation (i.e. data integrity)

Below are two articles / posts related to the performance benefits of typed XML. There is mention of XML Indexes performing much better with typed XML than with untyped. But, there is also mention of typed XML being worse for DML operations.

Related Question