SQL Query Not Working After XML Data Type Column Added

Hello EE,

An XML column has been added to to a table I frequently query using SSMS and I am now receiving the message below.  This happens when I SELECT * or just select the non-xml fields.  What is the workaround?  I am running SQL Server 2005, but connecting via SSMS 2008.

Thank you,
LVBarnes

Msg 9514, Level 16, State 1, Line 1
Xml data type is not supported in distributed queries. Remote object 'Server.Database.dbo.Table' has xml column(s).
LVL 5
Lawrence BarnesAsked:
Who is Participating?
 
SandeepratanCommented:
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.
ReplyQuote

Here is a work around:

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.
0
 
Lawrence BarnesAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.