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.

If you try to perform an update of the converted XML column:

--In source DB
UPDATE [LinkedServer].[DatabaseName].dbo.vw_NonXmlColumn
SET XmlColumn = '<new XML value>'
WHERE <condition>

You will encounter another error: 

Statement(s) could not be prepared.

Update or insert of view or function '[DatabaseName].dbo.view' failed because it contains a derived or constant field.

To resolve this, we can use "Instead of Update" trigger.

On source DB, create a trigger when the view update is executed: 

--In source DB
CREATE TRIGGER TR_TableName_InsteadOfUpdate ON vw_NonXmlColumn
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON

    UPDATE TableName
    SET XmlColumn = i.XmlColumn
    FROM TableName t
    INNER JOIN inserted i
        ON i.ID = t.ID --Unique identifier of the table
END

You can now use back the same query used to update the XML column via Linked Server successfully:

--In source DB
UPDATE [LinkedServer].[DatabaseName].dbo.vw_NonXmlColumn
SET XmlColumn = '<new XML value>'
WHERE <condition>

PS: Try to simplify the update statement by using variables if necessary, if there is any issue encountered when performing the update to the view.