How To (Not) Read XML Into SQL Server Using Azure Data Factory

Good news! This workaround is obsolete by now, as Azure Data Factory now natively supports XML datatype for datasets.

CREATE TABLE [dbo].[XML_Staging](
[XmlCol] [xml] NULL
) ON [PRIMARY]
Azure DataLake file formats
Sorry, no native XML support yet
Put a the input data is mapped to the XML column of the database table
SELECT TOP (30000) [CUSTOMERNO]
,[OPERATION]
,[EMAIL]
FROM [dbo].[CUSTOMERS]
FOR XML PATH, ROOT('Customers')
{
"dataRead": 4676487,
"dataWritten": 9352968,

"filesRead": 1,
"sourcePeakConnections": 2,
"sinkPeakConnections": 2,
"rowsRead": 1,
"rowsCopied": 1,
"copyDuration": 8,
"throughput": 570.86,
"errors": [],
...
CREATE TYPE [dbo].[XmlDumpType] AS TABLE(
[xml] xml NOT NULL
)
GO
CREATE PROCEDURE dbo.spOverwriteXmlStaging @inputs [dbo].[XmlDumpType] READONLY
AS
BEGIN
MERGE dbo.XML_Staging AS target
USING @inputs AS source
ON (1=2)
WHEN MATCHED THEN
UPDATE SET XmlCol = source.xml
WHEN NOT MATCHED THEN
INSERT (XmlCol)
VALUES (source.xml);
END
"sink": {
"type": "SqlMISink",
"preCopyScript": {
"value": "TRUNCATE TABLE [dbo].[XML_Staging];",
"type": "Expression"
},
"sqlWriterStoredProcedureName": "[dbo].[spOverwriteXmlStaging]",
"sqlWriterTableType": "XmlDumpType",
"storedProcedureTableTypeParameterName": "inputs"
},
SELECT TOP 1 XmlCol FROM dbo.XML_Staging
But no: data is still truncated
CREATE PROCEDURE dbo.spOverwriteXmlStaging @inputs [dbo].[XmlDumpType] READONLY
AS
BEGIN
DECLARE @XMLDOC xml;SET @XMLDOC = (SELECT TOP 1 xml FROM @inputs);INSERT INTO dbo.CUSTOMERS_FROM_XML
(
[CUSTOMERNO],
[OPERATION],
[EMAIL]
)
SELECT
doc.col.value('CUSTOMERNO[1]', 'bigint') AS [CUSTOMERNO],
doc.col.value('OPERATION[1]', 'nvarchar(100)') AS [OPERATION],
doc.col.value('EMAIL[1]', 'nvarchar(1000)') AS [EMAIL]
FROM @XMLDOC.nodes('//row') doc(col)
END

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store