|
|
Error Message
Server: Msg 305, Level 16, State 1, Line 1
The XML data type cannot be compared or sorted, except when
using the IS NULL operator.
Causes
The XML data type allows the storage of XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. Columns and variables of XML data type can be created and used to store XML instances.
|
The following general limitations apply to the XML data type:
- The stored representation of XML data type instances cannot exceed 2 GB.
- A subtype of a SQL_VARIANT instance cannot be used.
- Casting or converting to either TEXT or NTEXT data type is not supported. VARCHAR(MAX) or NVARCHAR(MAX) should be used instead.
- XML data types cannot be compared or sorted and therefore cannot be used in a GROUP BY statement.
- Used as a parameter to any scalar, built-in functions other than ISNULL, COALESCE and DATALENGTH, is not allowed.
- XML data types cannot be used as a key column in an index.
From the list of limitations outlined above, this error message is raised if an XML data type is compared, sorted or used in a GROUP BY statement. Here are a few examples on how this error message is generated:
-- Case #1: Comparing an XML local variable with NULL using the equality sign
DECLARE @XML XML
IF @XML = NULL
PRINT '@XML is NULL'
GO
Msg 305, Level 16, State 1, Line 3
The XML data type cannot be compared or sorted, except when using the IS NULL operator.
-- Case #2: Comparing two XML local variables
DECLARE @XML1 XML
DECLARE @XML2 XML
IF @XML1 > @XML2
PRINT '@XML1 is greater than @XML2'
GO
Msg 305, Level 16, State 1, Line 4
The XML data type cannot be compared or sorted, except when using the IS NULL operator.
-- Case #3: Using an XML column in the ORDER BY clause
DECLARE @XMLTable TABLE (
[XMLID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[XML] XML
)
SELECT * FROM @XMLTable
ORDER BY [XML]
GO
Msg 305, Level 16, State 1, Line 4
The XML data type cannot be compared or sorted, except when using the IS NULL operator.
-- Case #4: Using an XML column in the GROUP BY clause
DECLARE @XMLTable TABLE (
[XMLID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[XML] XML
)
SELECT [XML], COUNT(*)
FROM @XMLTable
GROUP BY [XML]
Msg 305, Level 16, State 1, Line 9
The XML data type cannot be compared or sorted, except when using the IS NULL operator.
Solution / Work Around:
As mentioned in the list of limitations of the use of XML data type, comparing, sorting and grouping by XML data types should be avoided. For each of the sample cases shown above of generating this error message, the following script provides ways of working around this error:
-- Case #1 -- Use IS NULL instead of equality (=)
DECLARE @XML XML
IF @XML IS NULL
PRINT '@XML is NULL'
GO
-- Case #2 -- Convert to VARCHAR(MAX)
DECLARE @XML1 XML
DECLARE @XML2 XML
IF CAST(@XML1 AS VARCHAR(MAX)) > CAST(@XML2 AS VARCHAR(MAX))
PRINT '@XML1 is greater than @XML2'
GO
-- Case #3 -- Convert to VARCHAR(MAX)
DECLARE @XMLTable TABLE (
[XMLID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[XML] XML
)
SELECT * FROM @XMLTable
ORDER BY CAST([XML] AS VARCHAR(MAX))
GO
-- Case #4 -- Convert to VARCHAR(MAX)
DECLARE @XMLTable TABLE (
[XMLID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[XML] XML
)
SELECT CAST([XML] AS VARCHAR(MAX)), COUNT(*)
FROM @XMLTable
GROUP BY CAST([XML] AS VARCHAR(MAX))
GO
|