n all the excitement about SQL Server 2005 (formerly codenamed Yukon), most people tend to focus on the .NET integration. But the native support for XML might turn out to have even more of an impact on the everyday working lives of DBAs. Native XML makes SQL Server an ideal knowledge management platform, a place for organizing memos, white papers, specifications, design documents, and corporate policies and procedures. In order to make sense of all this data we need a new query language: XQuery.
XQuery is the SQL of XML. And in many ways, the two languages are similar. Like SQL, XQuery does more than just queries; it's closer to a general purpose data-processing language designed specifically for XML. While you can certainly use it to extract various meaningful bits of data from XML documents, XQuery can also be used for deleting, inserting, and updating data.
XQuery in SQL Server 2005
To show off some of the new XQuery functionality, I made up a fictional (and slightly dysfunctional) company called CookieCo. This bakery stores all of its recipes in a simplistic relational database (\CookieCo.sql) of just three tables:
- Cookie. Each row represents a single cookie recipe.
- Ingredient. Each row represents one ingredient used in a recipe.
- Direction. Each row represents a single step in the directions of a recipe.
|Figure 1. Data Model: This Entity Relationship Diagram shows the three tables of a simple cookie-recipe database: cookie, ingredient, and direction. |
The idea of storing recipe information in a relational database may not be the best idea that CookieCo's IT department ever came up with. But there are a few advantages over, say, a plain text file:
- All recipes live in a single, secure location.
- Recipe data is stored in a display-neutral fashion.
- Recipes can be queried without writing any specialized parsers.
The technology people at CookieCo like to be cutting-edge, so once they heard about the new XML functionality built into the next version of SQL Server, they decided to store all their recipes in the database in a field of type XML. Each recipe gets its own document, which is also stored in the Cookie table right next to the relational data that it's replacing. So, the database contains two copies of every recipe: one copy in a relational format and one copy in XMLat least until the migration project is complete.
|What You Need
| You should be familiar with XML and T-SQL. To really follow along, you'll need SQL Server 2005. Subscribers can download the full beta from MSDN. Everyone else can get the express edition from Microsoft's SQL Server Developers Center.