s 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. Even 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.
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
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
|SQL Server 2005's XML functionality is markedly different from that in SQL Server 2000.|
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)
<Pitcher name="John Smoltz"
INSERT INTO Team (TeamDoc)
<Team name="Red Sox">
<Pitcher name="Petro Martinez"
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
types 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.