CODEWITHSUNDEEP
sql-serverxmlssisxsd
derlee
derlee

Reputation: 45

Abnormal data validation in XML

I will be receiving some confidential employee data in XML with separate XSD. It will be injected into our SQL Server (2016) using SSIS.

I was asked to edit the XSD schema as I want (for the data formats, i.e. change string to int, etc.).

I wish I had some data validation, like if I have 50k records of employees and 45k of them has some value that they definitely shouldn't have (i.e. isTerminated=1 or whatever), then it should fail the import (or validation).

Is that possible?

Upvotes: 1

Views: 40

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

No, SQL-Server can provide a schema out of its own meta data, but it is not very helpfull in validating against a foreign schema.

I was asked to edit the XSD schema as I want (for the data formats, i.e. change string to int, etc.).

You should not change this! The incoming schema describes the data you get. Especially if you want to set changes like string to int you will fairly probably get errors... The people designing the source database were not stupid hopefully and had a reason for a string column in this place...

I'd import this data into a staging table and check the data integrity first. Then use MERGE or simple INSERT INTO to write this lot into your tables doing all the needed casts and repairs you found before...

Upvotes: 2

Related Questions