XML Data Type & Columns

This article discusses the advantages and the limitations of the

data type in SQL Server,

and helps you to choose how to store XML data.

If your data is highly structured with a known schema, the relational model is likely to work

best for data storage. SQL Server provides the required functionality and tools you may need.

On the other hand, if the structure is semi-structured or unstructured, or unknown, you have to

give consideration to modeling such data.

XML is a good choice if you want a platform-independent model in order to ensure portability

of the data by using structural and semantic markup. Additionally, it is an appropriate option if

some of the following properties are satisfied:

Your data is sparse or you don’t know the structure of the data, or the structure of your

data may change significantly in the future.

Your data represents containment hierarchy, instead of references among entities, and

may be recursive.

Order is inherent in your data.

You want to query into the data or update parts of it, based on its structure.

If none of these conditions is met, you should use the relational data model. For example, if

your data is in XML format but your application just uses the database to store and retrieve the

data, an

column is all you require. Storing the data in an XML column has

additional benefits. This includes having the engine determine that the data is well formed or

valid, and also includes support for fine-grained query and updates into the XML data.

Following are some of the reasons to use native XML features in SQL Server instead of

managing your XML data in the file system:

You want to share, query, and modify your XML data in an efficient and transacted way.

Fine-grained data access is important to your application. For example, you may want to