SQL Query Not Working After XML Data Type Column Added

Posted on 2011-04-21
Last Modified: 2012-05-11
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,

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).
Question by:Lawrence Barnes
    LVL 22

    Assisted Solution

    by:Nico Bontenbal
    LVL 2

    Accepted Solution

    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.

    Here is a work around:


          Cast(a.XML_Data as XML) as XML_Data




                Cast(XML_Data as Varchar) as XML_Data
                [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.
    LVL 5

    Author Closing Comment

    by:Lawrence Barnes
    Thank you

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now