Hello guys, today I’ll talk about queries with Linked Server and XML Data.
When you try to make a SQL Server query using Linked Server, and the table has an XML Data you will receive the following message:
It easy solves the problem, you need to use an OPENQUERY like this:
SELECT
Name,
Description,
XML fields…
Other fields…
FROM
OPENQUERY ([Linked Server Name Here],
‘
SELECT
nvarchar1 as Name,
nvarchar2 as Description,
XML fields…..
Other fields…
FROM
[DATABASE NAME].[SCHEMA].[TABLE NAME 1] as TB1
INNER JOIN
[DATABASE NAME].[SCHEMA].[TABLE NAME 2] as TB2 ON TB1.ID = TB2.ID
WHERE ….
‘
)AliasName
Where …
Group by …
So, you just create a new select using OPENQUERY called the Linked Server and inside you use the “original” query.
Enjoy, see you!
Diego Pereira