Interesting error today:
When we query over linked server, we run into some restrictions. One such restrictions is this, where we could not query XML columns over LinkedServer connection.
For example, if the remote server table has a XML column in its structure, when we query the remote server for this table, we run this error:
-- -- Old query that throws this error -- SELECT ID, XMLColumn FROM LinkedServer01.DatabaseName.dbo.TableName GO
Error : Msg 9514, Level 16, State 1, Line 1
Xml data type is not supported in distributed queries.
Remote object has xml column(s)
Resolution:
Not sure why this limitation exists, but there is a workaround: OPENQUERY
In the past, we’ve covered some key benefits of OPENQUERY and how it allows us to circumvent some limitations of remote-table-value function calls. Today, we’ll see another benefit of OPENQUERY in circumventing the XML limitation of linked servers.
Rather than querying XML directly, we convert XML into NVARCHAR(MAX) and revert it back to XML once the data is on local server.
-- -- Modified query to circumvent the limitation -- SELECT ID, CONVERT(XML, XMLColumn) AS XMLcolumn FROM OPENQUERY(LinkedServer01, 'SELECT ID , CONVERT(NVARCHAR(MAX), XMLColumn) AS XMLColumn FROM DatabaseName.dbo.TableName') AS RemoteQuery GO
The XML data is retrieved as NVARCHAR(MAX) to the local machine; And immediately converted to XML before displaying.
_Sqltimes
Leave a comment