Linux Application Development (2nd Edition)

Why Store XML Data in SQL Server 2005?

One key feature added to SQL Server 2005 is the support for native XML data. SQL Server 2005 is an integrated platform for both XML and relational data, providing core database services such as concurrency, recovery, query and update language, and an execution engine and optimizer.

Here are some of the benefits of storing XML data in SQL Server 2005.

• You can run queries directly against the XML data. In versions before SQL Server 2005, XML data is stored as CLOBs, which have limited search capabilities.

• You can index the XML data and show query plans in the Showplan output.

• Many applications have stored XML data as files on a server, separate from the relational data. Keeping the XML and relational data in sync could be a problem, especially when dealing with failover issues and backup and restore issues. Storing XML directly in SQL Server 2005 eliminates this problem.

• The elements in an XML document are inherently part of the XML document, and their order is preserved in query results. Relational data is unordered, which means you must add order columns to enforce ordering. For example, for an estimating system, the items on each estimate must be stored with a line number column to keep them in the order in which they were entered by the estimator. Another example is an XML document containing paragraphs of text, such as a manuscript. In a relational database, a position column would be required to maintain order, but that's not necessary if you store the data as XML in SQL Server.

• With relational data, a highly normalized design often means a high quantity of tables to join to retrieve the data. These joins hurt performance.

• XML data can more easily represent recursive data and graphs.

• From a transactional perspective, XML data that is stored in SQL Server 2005 is durable, consistent, and recoverable.

In most cases, structured data should be stored in relational tables with columns, whereas the xml data type is suited for semi-structured and markup data.