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:
- query() – Used to extract XML fragments from an XML data type.
- value() – Used to extract a single value from an XML document.
- exist() – Used to determine if a specified node exists. Returns 1 if yes and 0 if no.
- modify() – Updates XML data in an XML data.
- node() – Shreds XML data into multiple rows (not covered in this blog post).
Reference : Pinal Dave (https://blog.sqlauthority.com)
14 Comments. Leave new
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.
Hi
When any single row of the selected set has an xml data column exceeding varchar(max) in size (8000 on our instance), the whole query fails.
Can you suggest how I can take the first 8000 characters of these problem records – perhaps truncating the xml field prior to casting or casting with truncation?
Many Thanks
Brett
Hi Brett,
How are you inserting the records? In SSIS there is feature to ignore and store such problem records.
Otherwise you can insert only left 8000 character using LEFT or SUBSTRING t-sql function.
Regards,
Pinal Dave
workaround that worked for me – create view to the XML-containing table on the remote server excluding(if you don’t need it) or converting the XML columns to nvarchar… this will “mask” it to the SQL engine enough to accept distributed query…
thanks!. the view was a quick an easy solution. when i tried openrowset still had some configuration issues that caused a different error than what started this posting.
great webiste
-Paul
Your website is a bible for SQL beginners. At least for me.
Thanks a lot
R
Hi ,
I am firing trigger on table which will i have to update one column on another server of table.. but i am getting error… as belows…….
Xml data type is not supported in distributed queries.
table which i have to update using trigger have one xml column but i am not updating that column..
Thanks….
Are the servers of the same version? Can you post the query?
better solution use import and export option
If you don’t need the XML data in your query you can build a view that does not contain the XML column.
How to insert rows into remote database table that has xml columns.
I am using like below,
INSERT INTO [External server].[dbname.[dbo].[table]
SELECT *
FROM [dbname].[dbo].[table_Archive]
where date > getdate()
I am getting error
Xml data type is not supported in distributed queries. Remote object ” has xml column(s).
Thanks
I have also found that one cannot INSERT from one XML column to another XML column in a different table. The workaround is:
CAST(CAST(XML_Column AS VarChar(max)) AS XML)
Thanks for all your very valuable info Pinal; I’m often reading your blog when I get some free time!