In this lesson, you learn how to use an XML data type inside your database through an example. This example shows how you can make a relational database schema dynamic. The example extends the Products table from the TSQL2012 database.
Suppose that you need to store some specific attributes only for beverages and other attributes only for condiments. For example, you need to store the percentage of recommended daily allowance (RDA) of vitamins only for beverages, and a short description only for condiments to indicate the condiment’s general character (such as sweet, spicy, or salty).
You could add an XML data type column to the Production.Products table of the TSQL2012 database; for this example, call it additionalattributes. Because the other product categories have no additional attributes, this column has to be nullable. The following code alters the Production.Products table to add this column.
ALTER TABLE Production.Products
ADD additionalattributes XML NULL;
Before inserting data in the new column, you might want to constrain the values of this column. You should use a typed XML, an XML validated against a schema. With an XML schema, you constrain the possible nodes, the data type of those nodes, and more. In SQL Server, you can validate XML data against an XML schema collection. This is exactly what you need for a dynamic schema; if you could validate XML data against a single schema only, you could not use an XML data type for a dynamic schema solution, because XML instances would be limited to a single schema. Validation against a collection of schemas enables support of different schemas for beverages and condiments. If you wanted to validate XML values only against a single schema, you would define only a single schema in the collection.
You create the schema collection by using the CREATE XML SCHEMA COLLECTION T-SQL statement. You have to supply the XML schema, an XSD document, as input. Creating the schema is a task that should not be taken lightly. If you make an error in the schema, some invalid data might be accepted and some valid data might be rejected.
The easiest way to create XML schemas is to create relational tables first, and then use the XMLSCHEMA option of the FOR XML clause. Store the resulting XML value (the schema) in a variable, and provide the variable as input to the CREATE XML SCHEMA COLLECTION statement.
The following code creates two auxiliary empty tables for beverages and condiments, and then uses SELECT with the FOR XML clause to create an XML schema from those tables.
Then it stores the schemas in a variable, and creates a schema collection from that variable.Finally, after the schema collection is created, the code drops the auxiliary tables.

— Auxiliary tables
CREATE TABLE dbo.Beverages
(
percentvitaminsRDA INT
);
CREATE TABLE dbo.Condiments
(
shortdescription NVARCHAR(50)
);
GO
— Store the Schemas in a Variable and Create the Collection
DECLARE @mySchema NVARCHAR(MAX);
SET @mySchema = N”;
SET @mySchema = @mySchema +
(SELECT *
FROM Beverages
FOR XML AUTO, ELEMENTS, XMLSCHEMA(‘Beverages’));
SET @mySchema = @mySchema +
(SELECT *
FROM Condiments
FOR XML AUTO, ELEMENTS, XMLSCHEMA(‘Condiments’));
SELECT CAST(@mySchema AS XML);
CREATE XML SCHEMA COLLECTION dbo.ProductsAdditionalAttributes AS @mySchema;
GO
— Drop Auxiliary Tables
DROP TABLE dbo.Beverages, dbo.Condiments;
GO
The next step is to alter the XML column from a well-formed state to a schema-validated
one.
ALTER TABLE Production.Products
ALTER COLUMN additionalattributes
XML(dbo.ProductsAdditionalAttributes);
You can get information about schema collections by querying the catalog views sys.xml_schema_collections ,  sys.xml_schema_namespaces, sys.xml_schema_components, and some
others views in the sys schema with names that start with xml_schema_. However, a schema collection is stored in SQL Server in tabular format, not in XML format. It would make sense to perform the same schema validation on the client side as well. Why would you send data to the server side if the relational database management system (RDBMS) will reject it? You can perform schema collection validation in Microsoft .NET code as well, as long as you have the schemas. Therefore, it makes sense to save the schemas you create with T-SQL in files in a file system as well. If you forgot to save the schemas in files, you can still retrieve them from SQL Server schema collections with the xml_schema_namespace system function. Note that the schema returned by this function might not be lexically the same as the original schema used when you created your schema collection. Comments, annotations, and white spaces are lost.However, the aspects of the schema used for validation are preserved.

Before using the new data type, you have to take care of one more issue. How do you avoid binding the wrong schema to a product of a specific category? For example, how do you prevent binding a condiments schema to a beverage? You could solve this issue with a trigger; however, having a declarative constraint, a check constraint, is preferable. This is why the code added namespaces to the schemas. You need to check whether the namespace is the same as the product category name. You cannot use XML data type methods inside constraints. You have to create two additional functions: one retrieves the XML namespace of the additionalattributes XML column, and the other retrieves the category name of a product.
In the check constraint, you can check whether the return values of both functions are equal. Here is the code that creates both functions and adds a check constraint to the Production.Products table.
— Function to Retrieve the Namespace
CREATE FUNCTION dbo.GetNamespace(@chkcol XML)
RETURNS NVARCHAR(15)
AS
BEGIN
RETURN @chkcol.value(‘namespace-uri((/*)[1])’,’NVARCHAR(15)’)
END;
GO
— Function to Retrieve the Category Name
CREATE FUNCTION dbo.GetCategoryName(@catid INT)
RETURNS NVARCHAR(15)
AS
BEGIN
RETURN
(SELECT categoryname
FROM Production.Categories
WHERE categoryid = @catid)
END;
GO
— Add the Constraint
ALTER TABLE Production.Products ADD CONSTRAINT ck_Namespace
CHECK (dbo.GetNamespace(additionalattributes) =
dbo.GetCategoryName(categoryid));
GO
The infrastructure is prepared. You can try to insert some valid XML data in your new column.
— Beverage
UPDATE Production.Products
SET additionalattributes = N’
<Beverages xmlns=”Beverages”>
<percentvitaminsRDA>27</percentvitaminsRDA>
</Beverages>’
WHERE productid = 1;
— Condiment
UPDATE Production.Products
SET additionalattributes = N’
<Condiments xmlns=”Condiments”>
<shortdescription>very sweet</shortdescription>
</Condiments>’
WHERE productid = 3;

To test whether the schema validation and check constraint work, you should try to insert some invalid data as well.
— String instead of int
UPDATE Production.Products
SET additionalattributes = N’
<Beverages xmlns=”Beverages”>
<percentvitaminsRDA>twenty seven</percentvitaminsRDA>
</Beverages>’
WHERE productid = 1;
— Wrong namespace
UPDATE Production.Products
SET additionalattributes = N’
<Condiments xmlns=”Condiments”>
<shortdescription>very sweet</shortdescription>
</Condiments>’
WHERE productid = 2;
— Wrong element
UPDATE Production.Products
SET additionalattributes = N’
<Condiments xmlns=”Condiments”>
<unknownelement>very sweet</unknownelement>
</Condiments>’
WHERE productid = 3;
You should get errors for all three UPDATE statements. You can check the data with the SELECT statement. When you are done, you could clean up the TSQL2012 database with the following code.
ALTER TABLE Production.Products
DROP CONSTRAINT ck_Namespace;
ALTER TABLE Production.Products
DROP COLUMN additionalattributes;
DROP XML SCHEMA COLLECTION dbo.ProductsAdditionalAttributes;
DROP FUNCTION dbo.GetNamespace;
DROP FUNCTION dbo.GetCategoryName;
GO