Home
%3CLINGO-SUB%20id%3D%22lingo-sub-369169%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2364%3A%20Azure%20SQL%20Database%20-%20'The%20type%20'xml'%20is%20not%20supported%20with%20external%20tables%20for%20sharded%20data.'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-369169%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Jan%2021%2C%202019%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20Hello%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20I%20worked%20on%20a%20case%20that%20our%20customer%20tried%20to%20use%20an%20external%20table%20to%20retrieve%20data%20and%20they%20got%20the%20error%3A%20'The%20type%20'xml'%20is%20not%20supported%20with%20external%20tables%20for%20sharded%20data.'%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bcode%20language%3D%22SQL%22%5D%20%3CBR%20%2F%3E%20CREATE%20EXTERNAL%20TABLE%20%5Bdbo%5D.%5Bord%5D(%20%3CBR%20%2F%3E%20ID%20BIGINT%20NOT%20NULL%2C%20%3CBR%20%2F%3E%20PRE_ORDER%20BIGINT%20NOT%20NULL%2C%20%3CBR%20%2F%3E%20ORDER_DATA%20XML%20NULL%20%3CBR%20%2F%3E%20)%20%3CBR%20%2F%3E%20WITH%20%3CBR%20%2F%3E%20(%20%3CBR%20%2F%3E%20DATA_SOURCE%20%3D%20RemoteDatabase%20%3CBR%20%2F%3E%20)%3B%20%3CBR%20%2F%3E%20%5B%2Fcode%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20They%20followed%20the%20steps%20provided%20on%20our%20previous%20%3CA%20href%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fazuresqldbsupport%2F2018%2F11%2F14%2Fcross-database-query-in-azure-sql-database%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20article%26nbsp%3B.%20%3C%2FA%3E%20The%20error%20message%20is%20expected%20because%20as%20we%20could%20see%20in%20this%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-elastic-query-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20URL%20%3C%2FA%3E%20%2C%20XML%20field%26nbsp%3Bis%20a%20LOB%20type%20that%20is%20not%20supported%20to%20use%20External%20Tables.%20The%20workaround%20is%20to%20cast%20this%20type%20to%20nvarchar(max)%20using%20a%20view%20and%20cast%20again%20when%20you%20retrieve%20the%20information%20to%20XML%2C%20let%20me%20show%20you%20an%20example.%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20I%20create%20two%20databases%3A%20%3C%2FSTRONG%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bcode%20language%3D%22SQL%22%5D%20%3CBR%20%2F%3E%20CREATE%20DATABASE%20OriginDB(EDITION%3D'Standard'%2C%20Service_Objective%3D'S0')%3B%20%3CBR%20%2F%3E%20CREATE%20DATABASE%20RemoteDB(EDITION%3D'Standard'%2C%20Service_Objective%3D'S0')%3B%20%3CBR%20%2F%3E%20%5B%2Fcode%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20In%20Master%20database%20I%20performed%3A%20%3C%2FSTRONG%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bcode%20language%3D%22SQL%22%5D%20%3CBR%20%2F%3E%20CREATE%20LOGIN%20RemoteLogger%20WITH%20PASSWORD%3D'StrongPassword'%3B%20%3CBR%20%2F%3E%20CREATE%20MASTER%20KEY%20ENCRYPTION%20BY%20PASSWORD%3D'Credentials123!'%20%3CBR%20%2F%3E%20%5B%2Fcode%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20In%20the%20database%20RemoteDB%3A%20%3C%2FSTRONG%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bcode%20language%3D%22SQL%22%5D%20%3CBR%20%2F%3E%20CREATE%20USER%20RemoteLogger%20FOR%20LOGIN%20RemoteLogger%3B%20%3CBR%20%2F%3E%20CREATE%20TABLE%20%5Bdbo%5D.%5Bord%5D(%20%3CBR%20%2F%3E%20ID%20BIGINT%20NOT%20NULL%2C%20%3CBR%20%2F%3E%20PER_ORDER%20BIGINT%20NOT%20NULL%2C%20%3CBR%20%2F%3E%20ORDER_DATA%20XML%20NULL%20%3CBR%20%2F%3E%20)%20%3CBR%20%2F%3E%20CREATE%20VIEW%20%5Bdbo%5D.%5Bord_View%5D%20%3CBR%20%2F%3E%20AS%20%3CBR%20%2F%3E%20SELECT%20ID%2C%20PER_ORDER%2CCONVERT(NVARCHAR(MAX)%2CORDER_DATA)%20AS%20ORDER_DATA%20FROM%20%5Bdbo%5D.%5Bord%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20GRANT%20SELECT%20ON%20%5Bdbo%5D.%5Bord_View%5D%20TO%20RemoteLogger%20%3CBR%20%2F%3E%20%5B%2Fcode%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20In%20the%20OriginDB%20%3C%2FSTRONG%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bcode%20language%3D%22SQL%22%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CREATE%20DATABASE%20SCOPED%20CREDENTIAL%20AppCredential%20WITH%20IDENTITY%20%3D%20'RemoteLogger'%2C%20SECRET%3D'StrongPassword'%3B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CREATE%20EXTERNAL%20DATA%20SOURCE%20RemoteDatabasev1%20%3CBR%20%2F%3E%20WITH%20%3CBR%20%2F%3E%20(%20%3CBR%20%2F%3E%20TYPE%3DRDBMS%2C%20%3CBR%20%2F%3E%20LOCATION%3D'servername.database.windows.net'%2C%20--%20Change%20the%20servername%20for%20your%20server%20name.%20%3CBR%20%2F%3E%20DATABASE_NAME%3D'RemoteDB'%2C%20%3CBR%20%2F%3E%20CREDENTIAL%3D%20AppCredential%20%3CBR%20%2F%3E%20)%3B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CREATE%20EXTERNAL%20TABLE%20ord_View%20%3CBR%20%2F%3E%20(%20%3CBR%20%2F%3E%20ID%20BIGINT%20NOT%20NULL%2C%20%3CBR%20%2F%3E%20PER_ORDER%20BIGINT%20NOT%20NULL%2C%20%3CBR%20%2F%3E%20ORDER_DATA%20nvarchar(max)%20NULL%20%3CBR%20%2F%3E%20)%20%3CBR%20%2F%3E%20WITH%20%3CBR%20%2F%3E%20(%20%3CBR%20%2F%3E%20DATA_SOURCE%20%3D%20RemoteDatabaseV1%20%3CBR%20%2F%3E%20)%3B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20SELECT%20ID%2C%20PER_ORDER%2Cconvert(XML%2CORDER_DATA)%20AS%20ORDER_DATA%20FROM%20ord_View%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5B%2Fcode%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Enjoy!!!%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-369169%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jan%2021%2C%202019%20Hello%2CI%20worked%20on%20a%20case%20that%20our%20customer%20tried%20to%20use%20an%20external%20table%20to%20retrieve%20data%20and%20they%20got%20the%20error%3A%20'The%20type%20'xml'%20is%20not%20supported%20with%20external%20tables%20for%20sharded%20data.%3C%2FLINGO-TEASER%3E
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!!!