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

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
OneBitAhead

OneBitAhead

The tech staff of OneBitAhead GmbH, putting the web stack to work. Here to discuss daily bits & bytes. #javascript #nodejs #webcomponents #rdbms #php