Azure Data Factory Copy Data using XML Source

Question

Lets assume I have a simple XML file source which I've mapped to a corresponding sink in my SQL server database.

<Date Date="2020-03-13Z">
    <Identification>
        <Identifier>Maverick</Identifier>
    </Identification>
    <Pilot HomeAirport="New York">
        <AirportICAOCode>USA</AirportICAOCode>
    </Pilot>
</Date>

And then the schema

CREATE TABLE pilots
identifier VARCHAR(20),
ICAO_code VARCHAR(3)
)

I created a stored procedure in my sql server database that takes an input of the user-defined table type pilots_type which corresponds to the above schema to merge my data correctly.

But the pipeline fails when run with the error:

{
"errorCode": "2200",
"message": "ErrorCode=UserErrorInvalidPluginType,'Type=Microsoft.DataTransfer.Common.Shared.PluginNotRegisteredException,Message=Invalid type 'XmlFormat' is provided in 'format'. Please correct the type in payload and retry.,Source=Microsoft.DataTransfer.ClientLibrary,'",
"failureType": "UserError",
"target": "Sink XML",
"details": []
}

See image
Here the source is a blob that contains the XML.

Is XML not supported as a source after all?

I followed the examples on this link except for using an XML source cloudfronts.com/how-to-upsert-records-in-sqlsink-through-adf — Sixsmith
Do you mean that you used stored procedure as the sink in the Copy Actity? Or you used a data flow? — Joseph Xu
@JosephXu I invoke the stored procedure in the Copy Activity — Sixsmith
Do you have any parameters in your store procedure and do you want to transfer the properties of the xml file as the parameters? — Joseph Xu

Joseph Xu Joseph Xu · Accepted Answer · 2020-09-21T10:05:41

XML is supported as a source.
I've made a same test according to your sample xml file and sql table successfully.

I created a Table Type named ct_pilot_type:

CREATE TYPE ct_pilot_type AS TABLE(
identifier  nvarchar(MAX),
ICAO_code nvarchar(MAX)
)

I created the stored procedure named spUpsertPolit:

CREATE PROCEDURE spUpsertPolit

@polit ct_pilot_type READONLY

AS

BEGIN

MERGE [dbo].[pilot_airports] AS target_sqldb

USING @polit AS source_tblstg

ON (target_sqldb.identifier = source_tblstg.identifier)

WHEN MATCHED THEN

UPDATE SET

identifier = source_tblstg.identifier,

ICAO_code = source_tblstg.ICAO_code


WHEN NOT MATCHED THEN

INSERT (

identifier,

ICAO_code

)

VALUES (

source_tblstg.identifier,

source_tblstg.ICAO_code

);

END

I set the sink in the Copy activity:

I set the mapping:

It cpoied successfully:
The result shows:

Azure Data Factory Copy Data using XML Source

1 Answers