Share via

Implementing XML in SQL Server

The xml data type lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. You can create columns and variables of the xml type and store XML instances in them.

You can optionally associate an XML schema collection with a column, a parameter, or a variable of the xml data type. The schemas in the collection are used to validate and type the XML instances. In this case, the XML is said to be typed.

The xml data type and associated methods help integrate XML into the relational framework of SQL Server. For more information, see xml Data Type Methods.  

Limitations of the xml Data Type

Note the following general limitations that apply to the xml data type:

  • The stored representation of xml data type instances cannot exceed 2 GB.

  • It cannot be used as a subtype of a sql_variant instance.

  • It does not support casting or converting to either text or ntext. Use varchar(max) or nvarchar(max) instead.

  • It cannot be compared or sorted. This means an xml data type cannot be used in a GROUP BY statement.

  • It cannot be used as a parameter to any scalar, built-in functions other than ISNULL, COALESCE, and DATALENGTH.

  • It cannot be used as a key column in an index. However, it can be included as data in a clustered index or explicitly added to a nonclustered index by using the INCLUDE keyword when the nonclustered index is created.

See the topics in this section for more specific limitations of the xml data type.

In This Section



XML Data Type Variables and Columns

Describes how to create, modify, and use xml data type variables and columns.

Typed XML Compared to Untyped XML

Defines typed and untyped XML. Describes XML schemas and explains how to register an XML schema collection.

Generating XML Instances

Describes different methods for generating XML instances.

xml Data Type Methods

Describes the xml data type methods.

Setting Options (XML Data Type)

Describes the options that you must set when you query xml data type columns or variables.

Adding Namespaces Using WITH XMLNAMESPACES

Describes how to add namespaces by using a WITH XMLNAMESPACES clause.

XML Data Modification Language (XML DML)

Describes the XML Data Modification Language and its three keywords.

Indexes on XML Data Type Columns

Describes how to create, modify, and use primary and secondary XML indexes.

Serialization of XML Data

Explains how XML data is serialized and describes entitization of XML characters.

Working with the XML Data Type in Applications

Describes the options that are available to you for working with the xml data type in applications.

xml Data Type Representation in the AdventureWorks2008R2 Database

Describes the xml type columns in the AdventureWorks2008R2 database.