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
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.

1 comment:

Anesha said...

Hi Nice Blog. Data xml data conversion can range from a simple one for one import to a complex procedure where non-relational data needs to be imported, validated, cleansed and split up into multiple tables in a new relational database structure.