I tried to execute a query against a remote server when I got the following error:
Msg 9514, Level 16, State 1, Line 1
Xml data type is not supported in distributed queries. Remote object 'OPENROWSET' has xml column(s).
I came across the following remark in the Books Online (Guidelines for Using Distributed Queries):
Tables that have xml columns cannot be queried, even if the query accesses non-xml columns of the table.
This statement is not entirely true, when querying only non-xml fields it seems to work just fine.
Repro script (SQLCMD mode)
:CONNECT myServer
use myDB
GO
CREATE TABLE myXml
(ID int, someXml xml)
GO
:CONNECT myRemoteServer
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=myServer;Trusted_Connection=yes;',
'SELECT ID from myDB.dbo.myXml') -- should work just fine
GO
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=myServer;Trusted_Connection=yes;',
'SELECT ID, someXml from myDB.dbo.myXml') -- raises the above mentioned error
GO
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=myServer;Trusted_Connection=yes;', 'SELECT ID, CAST(someXml as varchar(max)) from myDB.dbo.myXml') -- workaround?
GO
If I find an explanation for this limitation I will post an update but I haven't figured out why this is not possible yet.
