How to do query for linked srever (distributed queries) having xml column in tables

Question

If i have xml datatype columns in table then if i run distributed query for linked server will give error.

SELECT * FROM linkedservername.Database2.dbo.Table1 will give error "Xml data type is not supported in distributed queries." So i tried by using following

select Cast(Xml_abc as XML) as Xml_abc from OPENQUERY([linkedservername],'SELECT Cast(Xml_abc as NVARCHAR(MAX)) as Xml_abc FROM Database2.dbo.Table1')

here Xml_abc is column name with xml datatype.

The query resulted in showing rows with only xml_abc column.

But if i want full table to display then what should i do?Because i have so many column in table and only one column with xml datatype.So giving every column name in select statement is difficult for me.Is there any shortcut method to do it?

Answer 1

Use a tool to write the script for you, eg Management Studio.

In Object Explorer, find your table, right-click it, then:

Script Table As > SELECT To > New Query Editor Window

Edit your scripted query as required.  Also bear in mind, if your XML is large, do you really want to pull it across a linked server via two CASTs?  Performance is not going to be great.

I think you may also run into the 8000 character limitation also despite using NVARCHAR(MAX).