3

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?

7

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.

| improve this answer | |
  • Thanks for the answer. Your answer is also awesome! I had never run into XML schemas that are stored in a database before so when I read about this while studying for the 70-461 exam I did not understand what they were used for until after reading the answers to my question. – h24601 Nov 15 '16 at 13:54
  • @h24601 You're welcome. I just updated my answer with two links that I found related to the performance benefits of typed XML, including mention of XML Indexes being better with typed XML than with untyped. – Solomon Rutzky Nov 15 '16 at 20:20
3

Per this concise writeup (paraphrased):

There needs to be an agreement between the sender and receiver about the structure and content of an XML document. An XSD (XML Schema Definition Language) Schema can be used to enforce this contract and validate the XML data being exchanged.

SQL Server stores XML schemas as 'XML Schema Collections', representing SQL Server objects, such as tables, views or stored procedures.

Based on an XSD schema, you can create an XML Schema Collection that can be used to validate an XML data type, variable or column. An XML variable or column that is associated with an XML Schema Collection is known as TYPED XML. SQL Server validates a TYPED XML value against the rules defined in the schema collection.

INSERT or UPDATE operations will succeed only if the value being inserted or updated is valid as per the rules defined in the Schema Collection.

With this in mind, real-world examples include:

  • databases needing to produce RSS feeds
  • producing and storing valid web service calls
  • custom unstructured data
  • XML document templates.

The options are limitless. Schemas simply ensure that the XML you're saving is validated.

| improve this answer | |
  • one more example - when we have small number of key values, and unexpected number of counters, which we plan add on the way, such as properties of documents - in this case in classic way we will need add columns to the production database, or just store XML schema of properties in single column – a_vlad Nov 14 '16 at 21:32
  • Hi Radolph and a_vlad. I thank you for you replies to my question. To be more specific can you tell me how an application would utilize a stored XML schema in the applications database? I am familiar with FOR XML but what had me scratching my head is why store the XML schema in the database using CREATE XML SCHEMA COLLECTION blah AS ... What can a developer do with a XML schema that is stored in the database? Can it be used as a template for returning query results to the application? – h24601 Nov 14 '16 at 21:45
  • @h24601 I've edited my answer. – Randolph West Nov 14 '16 at 23:09
  • @Radolph. I thank you for the updated answer. That was awesome! I am going to be on the lookout for a tutorial that will walk me through the process of creating and using an XML schema in the database to validate data. – h24601 Nov 15 '16 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.