error message: The text, ntext, or image data type cannot be selected as DISTINC

I want to use For XML Explicit to get well-formatted XML from database. Everytime if I tried to select something in DB whose datatype is text, error message came out: The text, ntext, or image data type cannot be selected as DISTINCT.

I tried to change data type from text to varchar and set size to 8000, but result in query analyzer is weird--xml is not complete(cut in the middle).

I wonder if anyone here could help me out with this problem. Your reply will be highly appreciated!

ThanksQuery Analyzer will not return complete fields beyond a certain length (even in "results in text" mode). The only way I know of to overcome this limitation is to:

Declare @.Foo varchar(8000)

SELECT @.Foo = TextColumn FROM MyTable

PRINT @.Foo

You can also use VB, VBScript or VBA and use the Debug.Print functionality (or write the results to a file).

Regards,

Hugh Scott

Originally posted by heidil90
I want to use For XML Explicit to get well-formatted XML from database. Everytime if I tried to select something in DB whose datatype is text, error message came out: The text, ntext, or image data type cannot be selected as DISTINCT.

I tried to change data type from text to varchar and set size to 8000, but result in query analyzer is weird--xml is not complete(cut in the middle).

I wonder if anyone here could help me out with this problem. Your reply will be highly appreciated!

Thanks|||Thanks so much for helping me! Yesterday I figured out how to show the complete XML in Microsoft Internet Explore by running the utility configure SQL server XML support in IIS.

I am still wondering why I use CDATA directive in my FOR XML EXPLICIT for a field that has a text datatype can work in the first level of hierarchy, but not in the second or third level of hierarchy. Do you have any idea? Thanks in advance!

OK version:
select 1 as tag,
null as parent,
Employees.EmployeeID as [Employee!1!EmployeeID],
Employees.Notes as [Employee!1!!CDATA]
from Employees

for xml explicit
**************
Query batch completed.

=================================
==================================
wrong version:

select 1 as tag,
null as parent,
Employees.EmployeeID as [Employee!1!EmployeeID],
null as [Employee!2!!CDATA]
from Employees

union

select 2,1,
null,
Employees.Notes

from Employees
for xml explicit
*********************
Server: Msg 8163, Level 16, State 4, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.