Limitations of XML Data Type in SQL Server 2005

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.
Advertisements

One thought on “Limitations of XML Data Type in SQL Server 2005

  1. how to use COALESCE with xml datatype in sql 2005.

    SELECT COUNT(*) AS cnt FROM Report.tbl_Report_Info AS ReportInfo INNER JOIN
    Report.tbl_ReportSentDetails AS SendReport ON ReportInfo.ReportID = SendReport.Report_ID
    WHERE(ReportName LIKE COALESCE (@ReportName,ReportName)) AND
    (Report_Mailed_Date = COALESCE (@Report_Mailed_Date,Report_Mailed_Date))

    now i want to use COALESCE with Report Mailed To
    which is xml datatype

    for searching criteria.
    pls tell me solution

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s