vlad navazhylau's blog

Work around for XML data type not supported in Distributed Queries

http://www.msdner.com/dev-archive/84/19-85-847101.shtm

SELECT
Cast(a.XML_Data as XML) as XML_Data
FROM
OPENQUERY([LINKED SERVER NAME HERE],'
SELECT
Cast(XML_Data as Varchar) as XML_Data
FROM
[DATABASE NAME].[SCHEMA].[TABLE NAME]'
) A

Basically, the data is queried on the remote server, converts the XML data to a varchar, sends the data to the requesting server and then reconverts it back to XML.

StephenDudzic at 2007-9-3 22:09:22 >

"Re: XML data type not supported in Distributed Queries This is a limitation in SQL Server 2005. Columns of xml type or CLR type cannot be queried directly or referenced from one server to another - this means the following:

1. You cannot use a table or view that contains xml or clr type as 4-part name in your query
2. You need to cast the column to either nvarchar(max) or varbinary(max) or other appropriate type to use
3. If you have a table that has xml type for example then you need to create a view that contains all columns other than xml and query it instead. Or you can issue a pass-through query using OPENQUERY with the appropriate columns only."