XML Data Type Problems in SQL Server 2008 and Access 2003 ..

I am using Access 2003 via ODBC to connect to tables in a SQL Server
database. (The database was created using SQL Server 2005. I'm using SQL
Server 2008 Express to connect to copies of the database files for
development purposes.)

Several of the tables have fields with XML data types. After linking to the
tables from Access, I note that the data type for these XML fields, as
reported by Access, is "text" and has a length of only 255 characters. As a
result, most of the ~2500 bytes of data in the XML field is lost and all that
I see is the last ~180 bytes.

What options are available for working around this problem? I'm new to SQL
server. Thanks for any help in advance.

It appears that you will need to do one of two things.

First, you can try putting the database in 2005 compatibility mode...a
property of the database.

If that doesn't do the trick for you then cast the values as a varchar(max).

e.g.

SELECT t1.SomeID,
CAST(t1.SomeXML AS nvarchar(max)) AS SomeXML
FROM dbo.SomeTable t1

-Ted

"WeaselAce" wrote in message

>I am using Access 2003 via ODBC to connect to tables in a SQL Server
> database. (The database was created using SQL Server 2005. I'm using SQL
> Server 2008 Express to connect to copies of the database files for
> development purposes.)
>
> Several of the tables have fields with XML data types. After linking to
> the
> tables from Access, I note that the data type for these XML fields, as
> reported by Access, is "text" and has a length of only 255 characters. As
> a
> result, most of the ~2500 bytes of data in the XML field is lost and all
> that
> I see is the last ~180 bytes.
>
> What options are available for working around this problem? I'm new to
> SQL
> server. Thanks for any help in advance.