There are 2 connected servers.

When I try to query the database of another server to a table where there is an XML field, which I don’t even choose, I get an error that it is forbidden to make queries to tables with XML.

Is there any way around this?

There is an option via Export / Import to import everything to the server and make a request, but this is extra time ...

  • Write what does not suit in the answer, or mark it as true, please. - Alexey Shmelev

2 answers 2

In order to get a table with an xml-column via a remote query, you need to create a view on the remote server without an xml-column (if it is not needed) or convert it to varchar(max)/nvarchar(max) :

 CREATE VIEW xml_view AS SELECT CAST(xml_column as varchar(max)) as xml_column FROM xml_table

You can then use a remote query for this view.

 SELECT * FROM REMOTE_DB.database.dbo.xml_view

If on the main server you need a column of the xml type, and not varchar (max) / nvarchar (max), then you can additionally use a request to the view via openquery :

 SELECT cast(xml_column as xml) as xml_column FROM OPENQUERY(REMOTE_DB, 'SELECT xml_column FROM xml_view')

You can also directly access the remote table with xml using openquery :

 SELECT CAST(xml_column AS xml) as xml_column FROM OPENQUERY(REMOTE_DB, 'select cast(xml_column as varchar(max)) as xml_column from xml_table') xml_table;

    Alternatively, skip to NVARCHAR (MAX)

    • The very presence of a column to XML does not allow to request this table, the caste will not help. - minamoto
    • Well, in general, it will help. If you say it on the server side of the source (whether it is a query in OPENQUERY or a view), that is what I had in mind. But minus deserved, because I didn't specify - Donil