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/Soulution:
I CAST the retrieved the XML data type 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;
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
What are the permissions that are needed to be established on the remote server?
Is there a workaround using linked server?
Hi,
I am archiving data from source table having xml datatype and I am not allowed to create any new objects in the source.
So I am unable to use the common work around of converting the xml datatype to varchar while inserting data through link server.
Kindly help me to load xml data from source to target with link server.
Thanks,
Akila.