SQL SERVER – Fix : Error : Msg 9514 Xml data type is not supported in distributed queries. Remote object ‘OPENROWSET’ has xml column(s)

Msg 9514 means your query crossed a linked server and found an XML column on the other side. SQL Server will not move the XML type across that boundary. The fix is to stop asking for it as XML, and there are four ways to do that depending on whether you need the contents.

The Error

Msg 9514, Level 16, State 1, Line 1
Xml data type is not supported in distributed queries. Remote object
'LINKEDSRV.Sales.dbo.Documents' has xml column(s).

Read the object name in the message. It names the exact remote table that has the column, which saves you hunting through a join of six tables.

Why It Happens

XML in SQL Server is not text. It is a parsed, typed structure in its own internal format. The methods on it are part of the engine.

A distributed query hands values between two instances through the OLE DB layer. That layer cannot carry the XML type. So SQL Server stops before it starts, rather than shipping something it cannot rebuild.

A few other types behave the same way. Meet this with XML and you may meet it again with a user defined type.

You May Not Need the Column at All

Check first, because this is the usual answer. SELECT star pulled the column in and nobody wanted it.

-- fails
SELECT * FROM LINKEDSRV.Sales.dbo.Documents;

-- works, because the xml column is not named
SELECT doc_id, created_at, customer_id
FROM LINKEDSRV.Sales.dbo.Documents;

Here is another reason to name your columns. Build a view over the remote table that never mentions the XML column. That solves it for everybody.

Convert It on the Far Side

If you do need the contents, turn the XML into text where it lives. Then bring the text across. OPENQUERY runs the whole statement on the remote server.

SELECT *
FROM OPENQUERY(LINKEDSRV,
    'SELECT doc_id, CAST(payload AS nvarchar(max)) AS payload_text
     FROM Sales.dbo.Documents');

The conversion happens before anything crosses the wire. The boundary only ever sees nvarchar. Cast it back locally to query inside it:

SELECT doc_id,
       CAST(payload_text AS xml).value('(/order/total)[1]', 'decimal(18,2)') AS total
FROM OPENQUERY(LINKEDSRV,
    'SELECT doc_id, CAST(payload AS nvarchar(max)) AS payload_text
     FROM Sales.dbo.Documents');

One caution. CAST to nvarchar loses the XML declaration and can change whitespace. That matters if the document is signed or compared byte for byte.

Pull Out Only What You Want

Often you do not want the document. You want one value inside it. Ask the remote server for that value and nothing else.

SELECT *
FROM OPENQUERY(LINKEDSRV,
    'SELECT doc_id,
            payload.value(''(/order/total)[1]'', ''decimal(18,2)'') AS total,
            payload.value(''(/order/status)[1]'', ''varchar(20)'') AS status
     FROM Sales.dbo.Documents');

Note the doubled single quotes. Everything inside OPENQUERY is a string to the local parser. Every quote in the remote statement has to be escaped. People get this wrong, and the error is confusing because it comes from the remote server.

This version is usually much faster as well. You move a decimal instead of a document.

Push the Work Instead of Pulling It

When you need many rows regularly, stop querying across the link. Have the remote side write what you need into a table on your side, on a schedule.

A linked server query running every few minutes over a large table is slow and fragile anyway. This error is often pointing at a design worth changing.

Finding the Columns Before They Bite

Run this on the remote server so you know where the trouble is:

SELECT SCHEMA_NAME(t.schema_id) AS sch, t.name AS table_name, c.name AS column_name
FROM sys.columns AS c
JOIN sys.tables  AS t ON t.object_id = c.object_id
JOIN sys.types   AS ty ON ty.user_type_id = c.user_type_id
WHERE ty.name = 'xml'
ORDER BY sch, table_name;

Knowing which tables carry XML turns this from a surprise into a thing you plan around.

Msg 9514 is not a limit on XML, it is a limit on what a linked server can carry between two engines.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.