Msg 9514, Level 16, State 1, Line 1
Xml data type is not supported in distributed queries. Remote object ‘OPENROWSET’ has xml column(s).
This error happens when XML Data Type is queried using OPENROWSET. It was very interesting for me to find the workaround for this.
I CAST the retrieved the XML data type to VARCHAR(MAX).
SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;', 'SELECT GroupName, Name, DepartmentID, CAST(MyXML as VARCHAR(MAX)) FROM AdventureWorks.HumanResources.Department ORDER BY GroupName, Name') AS a;
Reference : Pinal Dave (https://blog.sqlauthority.com)