OPENQUERY Syntax Help

Question

I have to create a View from a Table that has a XML Data Type so I cannot use Dynamic SQL as it results in...

Xml data type is not supported in distributed queries

This is the Dynamic SQL That I am using that works fine for all other tables except for those tables in which there is a XML Data Type...

WHILE (@@Fetch_Status = 0)
	BEGIN


		SELECT	@SQL	= N'CREATE VIEW [' + [TABLE_SCHEMA] + N'].[' + [TABLE_NAME] + N'] AS SELECT ' + STUFF (
			(
				SELECT	N',' + '[' + [COLUMN_NAME] + ']' + ''
				FROM	[SQL-GC-UAT].[GC-UAT-REP].[INFORMATION_SCHEMA].[COLUMNS]
				WHERE	[TABLE_CATALOG] = 'GC-UAT-REP'
				AND		[TABLE_SCHEMA]		= @SchemaName
				AND		[TABLE_NAME]		= @TableName
				ORDER BY [ORDINAL_POSITION]
				FOR XML PATH (''), TYPE
			).[value] ('.', N'NVARCHAR(MAX)'),
			1,
			1,
			N''
																											)
						+ N' FROM [SQL-GC-UAT].[GC-UAT-REP].' + N'[' + [TABLE_SCHEMA] + N'].[' + [TABLE_NAME] + N'];'
		FROM	[SQL-GC-UAT].[GC-UAT-REP].[INFORMATION_SCHEMA].[TABLES]
		WHERE	[TABLE_SCHEMA]	= @SchemaName
		AND		[TABLE_NAME]		= @TableName
		;
		--PRINT @SQL
		--;
		EXECUTE [sp_executesql] @SQL
		;
		FETCH NEXT FROM [Cursor_Tables_Without_Views]
		INTO @SchemaName,
			@TableName
			;

	END
	;

I am struggling with the OPENQUERY Syntax and of course the ' and trying to remember that two '' equals one ' but I cannot seem to get this to work.

Can someone please help me out here as this is a simple CREATE VIEW based on the Table Contents of the Table found in my linked server [SQL-GC-UAT]

Thanks in advance for your review and am hopeful for a reply.

Answer 1

Hi @Bobby P,

Inability to query columns of XML data type is a limitation of the OPENQUERY via Linked Servers. A workaround for it is to convert XML data type into NVARCHAR(MAX). After that the OPENQUERY via Linked Servers will work.

Just cast the XML data type column like below:

SELECT ...
, xmldata = TRY_CAST(XMLcolumn AS NVARCHAR(MAX))
FROM ...