SQL Server 2005 - What's the XML Data Type For?

If it wasn't for ingenuity, horses would still be the best way to travel, bread wouldn't be sliced, and every datatype we would ever programatically need would be an int or a string. Fortunately, life is much more advanced than that, and that includes everything techie.

SQL Server 2000 was (and still is) an absolutely solid database platform. In comparison to its previous versions, it was the first time, in a long time, for Microsoft to have a stable, strong, and respectable database platform to market to the IT community (and market they did!). In fact, it's part of the reason they had so many years in between releases, it allowed them to re-write any mistakes, as well as take advantage of newer concepts that entered the technological realm.

What new concepts, you ask? For example, how about XML? Yes, I know, XML was included in SQL Server 2000. But, let's be honest, how useful did you find T-SQL to be when you used the "FOR XML" statement? Personally, it caused me to shudder at the thought of its use. Fortunately, XML adoption has increased dramatically over the past several years, and truly has become a standardized method of data analysis and delivery for all the latest platforms. This adoption allowed for the XML datatype to become a very important piece to SQL Server 2005.

Why should I use XML?

Good question! SQL Server is a high impact database platform, and, especially for .NET developers, it's a piece of cake to work with. However, software should be used for its strengths, not its weaknesses. For example, if you're a pro at Microsoft Excel, would you write a document with it? Just because it can doesn't necessarily mean you shouldn't use Microsoft Word instead.

XML and SQL Server are the same way. SQL Server is an excellent transactional database. Need to Insert/Update/Delete records, bring it on! Create and blow away TEMP tables because your bored... it's ok, SQL Server likes it.

Now, not that SQL Server isn't an excellent analytical platform as well, but if simplicity is what you're looking for, XML takes the cake. XML is perfect for data storage that you need to perform lookups on, or for application configuration files (yes ASP .NET developers, I'm referring to none other than the web.config file).

Bottom line, you can use SQL Server for your data storage needs, and there's obviously nothing wrong with doing so, but it certainly doesn't hurt to extend your data manipulation abilities.

So what good is the XML Datatype?

The idea behind this article is to explain the purpose of the XML Datatype in SQL Server 2005. Given all the information and drabble above, imagine storing analytical data in a field in your favorite database table. You get the relational power of data storage for your XML, while using minimal database resources when performing analytics.

The SQL Server XML Datatype allows users to directly reference an XML Data Document, stored within the XML typed field, and perform queries against this data.

Additionally, SQL Server 2005 is much more accomodating with its "FOR XML" T-SQL statements. For one, there are more options to shape and format its output. Secondly, with this "more usable" XML, you can now perform SELECT INTO statements to populate your XML datatype fields.

Finally, the XML-type fields come inherently built in with XPath and XQuery, keeping traditional XML querying tools alive, while providing the backend power of SQL Server.

Hopefully this article gets your mind churning towards idea's how how to make more use of XML, and the XML datatype, in your next applications.