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.
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 = ''
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 '' 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 ('
role="Closer"/>
');
INSERT INTO Team (TeamDoc)
VALUES ('
role="Starter"/>
');
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 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.