Monday, January 08, 2007

xml datatype and distributed queries

Recently I stumbled upon a peculiar limitation in SQL Server 2005.

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
(ID int, someXml xml)

:CONNECT myRemoteServer

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=myServer;Trusted_Connection=yes;',
'SELECT ID from myDB.dbo.myXml') -- should work just fine

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=myServer;Trusted_Connection=yes;',
'SELECT ID, someXml from myDB.dbo.myXml') -- raises the above mentioned error

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=myServer;Trusted_Connection=yes;', 'SELECT ID, CAST(someXml as varchar(max)) from myDB.dbo.myXml') -- workaround?

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.