If you are trying to update a table containing XML column via Linked Server in SQL Server, and you are not able to, you are not alone.
There are 2 main issues in this scenario:
1. Accessing a table containing XML column via Linked Server
2. Updating the XML value
When trying to select the XML column of a table via linked server:
SELECT col1, col2, XmlColumn
FROM [LinkedServer].[DatabaseName].dbo.TableName Error encountered:
Xml data type is not supported in distributed queries. Remote object '[LinkedServer].[DatabaseName].dbo.TableName' has xml column(s).
There are a few workarounds to resolve this (we will focus on using view in this post):
1. Use OPENQUERY
2. Create a view that selects a converted non-XML value from the XML column in the destination DB.
--In destination DB
CREATE VIEW vw_NonXmlColumn
AS
SELECT col1, col2,
XmlColumn = CONVERT(NVARCHAR(MAX), XmlColumn)
FROM TableName WITH(NOLOCK)Then, on the source DB where you create the Linked Server, you can access the view instead:
--In source DB
select col1, col2, XmlColumn
from [LinkedServer].[DatabaseName].dbo.vw_NonXmlColumnThis resolves our first issue: accessing a table containing XML column via Linked Server.
