Limitations of the xml Data Type

Article
12/03/2008

The following limitations apply to the xml data type:

Cannot be used as a subtype of a sql_variant instance
Does not support casting or converting to either text or ntext.
Does not support the following column and table constraints:
- PRIMARY KEY/ FOREIGN KEY
- UNIQUE
- COLLATE
  XML provides its own encoding. Collations apply to string types only. The xml data type is not a string type. However, it does have string representation and allows casting to and from string data types.
- RULE
Cannot be compared or sorted. This means an xml data type cannot be used in a GROUP BY statement.
Cannot be used in Distributed Partitioned Views.
It can be created in a materialized view. The materialized view cannot be based on an xml data type method. However, it can be cast to an XML schema collection that is different from the xml type column in the base table.
Cannot be used as a parameter to any scalar, built-in functions other than ISNULL, COALESCE, and DATALENGTH.
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.
The XML declaration PI in an instance is not preserved when the instance is stored in the database. For example:
```
CREATE TABLE T1 (Col1 int primary key, Col2 xml)
GO
INSERT INTO T1 values (1, '<?xml version="1.0" encoding="windows-1252" ?><doc></doc>')
GO
SELECT Col2
FROM T1
```
The result is <doc/>.
The XML declaration PI, for example, <?xml version='1.0'?>, is not preserved when storing XML data in an xml data type instance. This is by design. The XML declaration (<?xml ... ?>) and its attributes (version/encoding/stand-alone) are lost after data is converted to type xml. The XML declaration is treated as a directive to the XML parser. The XML data is stored internally as ucs-2. All other PIs in the XML instance are preserved.
The order of attributes in an XML instance is not preserved. When you query the XML instance stored in the xml type column, the order of attributes in the resulting XML may be different from the original XML instance.
By default, insignificant white spaces are not preserved. For example:
```
DECLARE @x xml
SET @x = '<root>      <child/>     </root>'
SELECT @x 
```
This returns the following:
```
<root><child/></root>
```
However, you can change this behavior. To preserve the white space for an xml data type instance, use the CONVERT operator and set its optional style parameter to 1. For example:
```
SELECT CONVERT(xml, N'<root>      <child/>     </root>', 1)
```
If the style parameter is not used or its value is set to 0, insignificant white space is not preserved for the conversion of the xml data type instance. For more information about how to use CONVERT operator and its style parameter when you convert string data to xml data type instances, see CAST and CONVERT (Transact-SQL).
Single quotation marks and double quotations marks around attribute values are not preserved. The attribute values are stored in the database as a name and value pair. The quotation marks are not stored. When an XQuery is executed against an XML instance, the resulting XML is serialized with double quotation marks around the attribute values.
```
DECLARE @x xml
-- Use double quotation marks.
SET @x = '<root a="1" />'
SELECT @x
GO
DECLARE @x xml
-- Use single quotation marks.
SET @x = '<root a=''1'' />'
SELECT @x
GO
```
Both queries return = <root a="1" />.
Namespace prefixes are not preserved. When you specify XQuery against an xml type column, the serialized XML result may return different namespace prefixes.
```
DECLARE @x xml
SET @x = '<ns1:root xmlns:ns1="abc" xmlns:ns2="abc">
            <ns2:SomeElement/>
          </ns1:root>'
SELECT @x
SELECT @x.query('/*')
GO
```
The namespace prefix in the result may be different. For example:
```
<p1:root xmlns:p1="abc"><p1:SomeElement/></p1:root>
```
If you copy and paste XML as a string literal into the Query Editor window in SQL Server Management Studio, you might experience [N]VARCHAR string encoding incompatibilities. This will depend on the encoding of your XML instance. In many cases, you may want to remove the XML declaration. For example:
```
<?xml version="1.0" encoding="UTF-8"?>
  <xsd:schema …
```
You should then include an N to make the XML instance an instance of Unicode. For example:
```
-- Assign XML instance to a variable.
DECLARE @X XML
SET @X = N'…'
-- Insert XML instance into an xml type column.
INSERT INTO T VALUES (N'…')
-- Create an XML schema collection
CREATE XML SCHEMA COLLECTION XMLCOLL1 AS N'<xsd:schema … '
```
SQL Server returns xml data type instances to the client as a result of different server constructs such as FOR XML queries that use the TYPE directive, or where the xml data type is used to return XML from SQL columns, variables, and output parameters. In client application code, the ADO.NET provider requests that this xml data type information be sent in a binary encoding from the server. However, if you are using FOR XML without the TYPE directive, the XML data returns as a string type. In any case, the client provider will always be able to handle either form of XML.

Limitations of the xml Data Type

See Also

Concepts

Other Resources

Help and Information

Additional resources