Making Sense of the XML DataType in SQL Server 2005 As database developers, many of us have had to dip our feet into the wide ocean of XML. It should come as good news that in SQL Server 2005, you can store XML in the database with a new XML datatype. Although this is good news, many developers have been storing XML in the database for some time now. Without implicit support for XML, developers have been shoving XML documents into text fields since XML’s inception. SQL Server 2000 included some XML features out of the box. Key among these features was the ability to return results as XML using the FOR XML clause. SQL Server 2005’s functionality is markedly different. In SQL Server 2005, XML is a genuine data type, which means that you can use XML as a column in tables and views, in T-SQL statements, or as parameters of stored procedures. You can now store, query, and manage XML documents directly in the database. | " |
SQL Server 2005's XML functionality is markedly different from what SQL Server 2000 provides.
| " |
More importantly, you can also now specify the schema to which your XML must conform. Aside from providing a mechanism to validate your XML in the database, this also allows you to describe complex types of data to be stored and to have an engine that enforces those rules. Using the XML Datatype The XML datatype is not substantially different than any other datatype in SQL Server. It can be used in any place you would ordinarily use any SQL datatype. For example, the following creates an XML variable and fills it with a XML: DECLARE @doc xml
SELECT @doc = '<Team name="Braves" />'
Although literal XML is useful, you can also fill an XML variable using a query and the SQL Server’s FOR XML syntax: SELECT @doc =
(SELECT * FROM Person.Contact FOR XML AUTO)
The XML datatype is not limited to use as a variable. You can also use the XML data type in table columns. You can assign default values and the NOT NULL constraint is supported: CREATE TABLE Team
(
TeamID int identity not null,
TeamDoc xml DEFAULT '<Team />' NOT NULL
)
Inserting XML data into tables is just a matter of specifying the XML to add in the form of a string: -- Insert a couple of records
INSERT INTO Team (TeamDoc)
VALUES ('<Team name="Braves">
<Players>
<Pitcher name="John Smoltz"
role="Closer"/>
</Players>
</Team>');
INSERT INTO Team (TeamDoc)
VALUES ('<Team name="Red Sox">
<Players>
<Pitcher name="Petro Martinez"
role="Starter"/>
</Players>
</Team>');
When creating instances of XML in SQL Server 2005, the only conversion is from a string to XML. Similarly, going in the reverse direction, you can only convert to a string. Converting to and from text and ntext is not allowed. Limitations of the XML Data Type Although the XML datatype is treated like many other datatypes in SQL Server 2005, there are specific limitations to how it is used. These limitations are: - XML types cannot convert to text or ntext data types.
- No data type other than one of the string types can be cast to XML.
- XML columns cannot be used in GROUP BY statements.
- Distributed partitioned views or materialized views cannot contain XML data types.
- Use of the sql_variant instances cannot include XML as a subtype.
- XML columns cannot be part of a primary or foreign key.
- XML columns cannot be designated as unique.
- Collation (COLLATE clause) cannot be used on XML columns.
- XML columns cannot participate in rules.
- The only built-in scalar functions that apply to XML columns are ISNULL and COALESCE. No other scalar built-in functions are supported for use against XML types.
- Tables can have only 32 XML columns.
- Tables with XML columns cannot have a primary key with more than 15 columns.
- Tables with XML columns cannot have a timestamp data type as part of their primary key.
- Only 128 levels of hierarchy are supported within XML stored in the database.
| & | | 
By: Shawn Wildermuth
Shawn Wildermuth is an eight-time Microsoft MVP (C#), member of the INETA Speaker’s Bureau and an author of six books and dozens of articles on .NET. Shawn is involved with Microsoft as a Silverlight Insider, Data Insider and Connected Technology Advisors (WCF/Oslo/WF). You can see him speaking at a variety of international conferences including SDC, VSLive, WinDev, MIX, DevTeach and DevReach. He currently teaches workshops around the country through his training company AgiliTrain (http://agilitrain.com). You can reach Shawn via his blog at http://wildermuth.com.
swildermuth@adoguy.com | Fast Facts | | SQL Server 2005 has elevated XML into a first class type of data, so developers no longer have to send entire XML documents across the wire to make minor modifications. In addition, Microsoft has added support for strong typing of XML documents based on XML Schema so the database itself can do the validation of the data submitted. | |
|