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