Updating Table Containing Xml Column via LinkedServer

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_NonXmlColumn

This resolves our first issue: accessing a table containing XML column via Linked Server.

Read More