Introduction to XML Data modeling in SQL Server 2005

Relational or XML data model

If your data is highly structured and has a known schema, the relational model may be most effective for data storage. Microsoft SQL Server provides the necessary features and tools that you might need. On the other hand, if the structure is flexible (semi-structured and unstructured) or unknown, you must properly consider how to model such data.

If you need a platform-independent model to ensure portability of structured and semantically tagged data, XML is a good choice. Also, if some of the following properties are true, it is an appropriate choice:

• Your data is sparse, or you do not understand the structure of the data, or the structure of the data may change significantly in the future.

• Your data represents the container hierarchy (as opposed to references in the entity) and may be recursive.

• Your data is in an intrinsic order.

• You want to query the data or update some of the data based on its structure.

If either of these conditions is not met, you should use the relational data model. For example, if your data is in XML format, but your application rarely uses a database to store and retrieve data, the [N]varchar (max) column meets all your needs. Storing data in an XML column can provide additional benefits-the engine checks the data format specification or is valid, and supports fine-grained querying and updating of XML data.

Reasons to store XML data in SQL Server 2005

Here are some reasons to use native XML functionality in SQL Server 2005 instead of managing XML data in a file system:

• You want to use the management capabilities of the database server to manage XML data (for example, backup, recovery, and Replication).

• You want to share, query, and modify XML data in an efficient manner and in transaction processing. Fine-grained data access is important for your application. For example, you might want to extract some sections within an XML document, or you might want to insert a new section instead of replacing the entire document.