Lesson Learned #64: Azure SQL Database - 'The type 'xml' is not supported with external tables for sharded data.'

First published on MSDN on Jan 21, 2019
Hello,

I worked on a case that our customer tried to use an external table to retrieve data and they got the error: 'The type 'xml' is not supported with external tables for sharded data.'

[code language="SQL"]
CREATE EXTERNAL TABLE [dbo].[ord](
ID BIGINT NOT NULL,
PRE_ORDER BIGINT NOT NULL,
ORDER_DATA XML NULL
)
WITH
(
DATA_SOURCE = RemoteDatabase
);
[/code]

They followed the steps provided on our previous article . The error message is expected because as we could see in this URL , XML field is a LOB type that is not supported to use External Tables. The workaround is to cast this type to nvarchar(max) using a view and cast again when you retrieve the information to XML, let me show you an example.

• I create two databases:

[code language="SQL"]
CREATE DATABASE OriginDB(EDITION='Standard', Service_Objective='S0');
CREATE DATABASE RemoteDB(EDITION='Standard', Service_Objective='S0');
[/code]

• In Master database I performed:

[code language="SQL"]
CREATE LOGIN RemoteLogger WITH PASSWORD='StrongPassword';
CREATE MASTER KEY ENCRYPTION BY PASSWORD='Credentials123!'
[/code]

• In the database RemoteDB:

[code language="SQL"]
CREATE USER RemoteLogger FOR LOGIN RemoteLogger;
CREATE TABLE [dbo].[ord](
ID BIGINT NOT NULL,
PER_ORDER BIGINT NOT NULL,
ORDER_DATA XML NULL
)
CREATE VIEW [dbo].[ord_View]
AS
SELECT ID, PER_ORDER,CONVERT(NVARCHAR(MAX),ORDER_DATA) AS ORDER_DATA FROM [dbo].[ord]

GRANT SELECT ON [dbo].[ord_View] TO RemoteLogger
[/code]

• In the OriginDB

[code language="SQL"]

CREATE DATABASE SCOPED CREDENTIAL AppCredential WITH IDENTITY = 'RemoteLogger', SECRET='StrongPassword';

CREATE EXTERNAL DATA SOURCE RemoteDatabasev1
WITH
(
TYPE=RDBMS,
LOCATION='servername.database.windows.net', -- Change the servername for your server name.
DATABASE_NAME='RemoteDB',
CREDENTIAL= AppCredential
);

CREATE EXTERNAL TABLE ord_View
(
ID BIGINT NOT NULL,
PER_ORDER BIGINT NOT NULL,
ORDER_DATA nvarchar(max) NULL
)
WITH
(
DATA_SOURCE = RemoteDatabaseV1
);

SELECT ID, PER_ORDER,convert(XML,ORDER_DATA) AS ORDER_DATA FROM ord_View

[/code]

Enjoy!!!