XML support and exception handling for SQL Server 2005

This article gives the advantages of typed and untyped XML columns, migrating SQL Server 2000 databases to SQL Server2005, and how new exception handling works, and if the schema of the table changes, how do I perform the operation, etc. for your reference!

SQL Server 2005 includes several important improvements to the Transact-SQL (T-SQL) language. One of the additions is a new trigger that fires when the data definition language (DDL) statement is run. DDL triggers are useful when you are tracking or protecting the creation and modification of database objects, or making changes to the database server. Another new feature involves exception handling, which leaps a step forward in exception handling because it contains try/catch blocks. Another new set of features, centered on the new XML data type, is a great way to manage XML data in an enterprise application that interacts with SQL Server. XML documents or fragments can now be stored in XML columns, bound to schemas, and can be queried with XQuery syntax. This article summarizes all kinds of questions to make detailed answers.

Q: What are the advantages of typed and untyped XML columns?

A: Untyped XML columns can store all of the format-specification XML fragments or documents, and typed XML columns can be bound to XML schemas. Non-typed XML is useful if you are unsure which schema the XML will follow. For example, if you must use some XML from another application, but you cannot determine which XML schema the data will follow, you can do so by using an untyped XML column. Of course, untyped XML can also cause other problems. For example, you cannot program against a schema, so it is difficult to use XML efficiently. Sometimes, however, untyped XML cannot be avoided. Creating an untyped XML column is simple, as follows:

CREATE TABLE Foo(FooID INT, someXml XML)

Another result of schemas not bound to XML columns is that SQL server®2005 stores untyped XML as a string. Is that a good thing or a bad thing? In fact, both good and bad. storing XML as a string gives you more flexibility to store any XML fragment or document. Depending on the business rules of your application, you may need this approach. On the other hand, storing XML as a string means that it can neither be stored efficiently nor efficiently search for XML data compared to typed XML. And neither tells what SQL Server XML will contain, nor does it tell the data types of the hierarchy and XML nodes. But keep in mind that just because these untyped XML columns doesn't mean they can accept any format you design. Typed and untyped XML columns still accept only well-formed XML.