SQL2005 data modeling with XML data types

The same or different table

An XML data type column can be created in a table that contains other relational columns, or in a stand-alone table that has a foreign key relationship with the primary table.

Create an XML data type column in the same table when one of the following conditions is true:

• Your application performs data retrieval on an XML column and does not require an XML index on the XML column. Or

• You need to generate an XML index on an XML data type column, and the primary key of the main table is the same as its clustered key. For more information, see the section indexed XML data type columns.

Create an XML data type column in a separate table when the following conditions are true:

• You need to generate an XML index on an XML data type column, but the primary key of the primary table differs from its clustered key, or the primary table does not have a primary key, or the primary table is a heap (that is, there are no clustered keys). This may be true if the primary table already exists.

• You do not want table scans to slow down because of the presence of XML columns in the table, whether they are stored in rows or stored in rows, and occupy space.

Granularity of XML data

The granularity of XML data stored in an XML column is critical for locking and updating attributes. SQL Server uses the same locking mechanism for XML and non-XML data. Therefore, row-level locking causes all XML instances in the corresponding row to be locked. When the granularity is large, locking large XML instances for updates can lead to a decrease in throughput in multiuser situations. On the other hand, serious decomposition can lose object encapsulation and increase the cost of the regroup.

Updating an XML instance replaces an existing instance with an updated instance, even if only the value of a single property is modified. The greater the granularity of XML data, the higher the cost of updating. A smaller XML instance can produce higher update performance.

For good design, it is important to maintain a balance between data modeling needs and locking and updating features.

Non-typed, typed, and constrained XML data types

The SQL Server SQL-2003 XML data type implements the ISO-standard XML data type. Therefore, it can store well-formed XML 1.0 documents and so-called XML content fragments with text nodes and any number of top-level elements in an untyped XML column. The system checks whether the data is well-formed, does not require that columns be bound to an XML schema, and rejects data that is not well-formed in the extended sense. The same is true for untyped XML variables and parameters.

If you have an XML schema that describes XML data, you can associate these schemas with XML columns to produce typed XML. XML schemas are used to validate data, perform more accurate type checking than untyped XML during query and data modification statement compilation, and optimize storage and query processing.

Use untyped XML data types under the following conditions:

• You do not have schemas that correspond to XML data

• You have a schema, but you do not want the server to validate data. This can sometimes occur when the application performs client validation before the data is stored in the server, or when it temporarily stores XML data that is not valid for the schema, or when it uses a schema component (such as KEY/KEYREF) that is not supported in the server.

Use typed XML data types under the following conditions:

• You have schemas that correspond to XML data, and you want the server to validate XML data according to XML schemas.

• You want to take full advantage of storage and query optimization based on type information.

• You want to use the type information more fully during the compilation of the query.

typed XML columns, parameters, and variables can store XML documents or content-you must specify them as flags (document or content, respectively) when you declare them. Also, you must provide a collection of XML schemas. If every XML instance has exactly one top-level element, specify DOCUMENT; otherwise, use the CONTENT. The query compiler uses the DOCUMENT tag to infer a unique top-level element in the type check of the query compilation process.

In addition to typing XML columns, you can use relationship (column or row) constraints on typed or untyped XML data type columns. Use constraints under the following conditions:

• Business rules cannot be represented in XML schemas. For example, a florist's shipping address must be within a 50-mile radius of its place of business, which can be written as a constraint on an XML column. This constraint may involve XML data type methods.

• Your constraints involve other XML columns or non-XML columns in the table. An example of this is forcing the Customer ID (/customer/@CustId) that exists in the XML instance to match the values in the Relational CustomerID column.

Document type definition (DTD)

XML data type columns, variables, and parameters can be typed using XML schemas instead of DTDs. However, for untyped and typed XML, you can use the inline DTD to provide default values to replace the entity references with their extended form.

You can use Third-party tools to convert DTDs into XML Schema documents and load XML schemas into the database.