XML through linked server

The other day I was loading some data through a linked server. Unfortunately I ran into problem since the data i was retreiving contained an XML datatype column.

I got the following error message: Msg 9514, Level 16, State 1, Line 1 Xml data type is not supported in distributed queries.

Remote object ‘SERVER.DATABASE.OWNER.TABLE’ has xml column(s). Fortunately as always there is a way around this. If i cast the XML datatype column to nvarchar(max) and then back to XML again it works. Example: SELECT Cast(SQL.XML_Data as XML) FROM OPENQUERY(SERVER,’select cast(XMLColumn as nvarchar(max)) as XML_Data from DATABASE.SCHEMA.TABLE’ ) SQL   Problem solved 🙂
/Jan Nieminen