SQL Server Business Intelligence

SQL SERVER 2005 & 2008 IS THE MAIN THEME OF THIS BLOG

Thursday, February 02, 2006

How can you validate XML Schema against incoming XML document?

For example if you are getting customers in an XML file format to load into your data warehouse using SSIS. Initial file you have received was in a perfect format. You built Schema using a specified XML location. Generate the schema using “Generate XSD” and saved it to a given location. You mapped the XML Output to a destination table in SQL Server. All ran through fine during first week. Second week the XML file that was sent to you by source system has been changed. You want to validate the XML source against XML Schema. How can I do that? If there is a difference between the source file and schema I want to fail the package.

There is a component called XML Task in Control Flow, which could help you to do that. You can do the following using the XML Task like Diff, XSLT, XPATH, Merge, Patch & Validate.
http://www.smart-bi-solutions.com/BlogStuff/020206_Pic1.jpg

The Validate is the Operation Type you would you to validate the XML Source file against XML Schema. Once you have selected the OperationType, make sure XSD is selected in ValidationType (which is default). Please be aware that the FailOnValidationFail is set to False by default. If you leave it with default value for FailOnValidationFail, the package will NOT fail even if the XSD and XML doesn’t match. Therefore you need to change FailOnValidationFail value to True if you want your package to fail.

Thanks
Sutha

1 Comments:

  • At 5:30 AM, Blogger Unknown said…

    Is it possible to get the schema validation errors in a SSIS Package

     

Post a Comment

<< Home