SQL SERVER – Fix : Error : Msg 9514 Xml data type is not supported in distributed queries. Remote object ‘OPENROWSET’ has xml column(s)

In this blog post we are going to learn how to fix XML Data Type related 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).

This error happens when XML Data Type is queried using OPENROWSET. It was very interesting for me to find the workaround for this.

Fix/WorkAround/solution:
I CAST the retrieved the XML data types to VARCHAR(MAX).

Example:

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;

Let me know if you have any further question about this error.

What are XML Data Type Methods?

The XML data types were first introduced with SQL Server 2005. This data type continues with SQL Server 2008 were expanded XML features are available, most notably is the power of the XQuery language to analyze and query the values contained in your XML instance.

There are five data type methods available in SQL Server 2008:

1. query() – Used to extract XML fragments from an XML data type.
2. value() – Used to extract a single value from an XML document.
3. exist() – Used to determine if a specified node exists. Returns 1 if yes and 0 if no.
4. modify() – Updates XML data in an XML data.
5. node() – Shreds XML data into multiple rows (not covered in this blog post).

Reference : Pinal Dave (https://blog.sqlauthority.com)