Validate XML documents and get rich error output by enabling the
property of the XML Task. For more info, see
Validate XML with the XML Task
in the SSIS documentation.
SQL Server Integration Services (SSIS) introduced the
property in SQL Server 2012 Service Pack 2 in July of 2014. This new property was not announced or documented at the time of its original release. The property is also available in SQL Server 2014 and in SQL Server 2016.
The following screen shot shows the
XML Task Editor
with the settings required for XML validation with rich error output.
property was available, XML validation by the XML Task returned only a true or false result, with no information about errors or their locations. Now, when you set
to True, the output file contains detailed information about every error including the line number and the position. You can use this information to understand, locate, and fix errors in XML documents.
The XML validation functionality scales easily for large XML documents and large numbers of errors. Since the output file itself is in XML format, you can query and analyze the output. For example, if the output contains a large number of errors, you can group the errors by using a Transact-SQL query, as described in this topic.
Sample output for XML that's valid
Here is a sample output file with validation results for a valid XML file.
<error line="5" position="26">The 'ApplicantRole' element is invalid - The value 'wer3' is invalid
according to its datatype 'ApplicantRoleType' - The Enumeration
<error line="16" position="28">The 'Phone' element is invalid - The value 'we3056666666' is invalid
according to its datatype 'phone' - The Pattern constraint failed.</error>
Analyze XML validation output with a Transact-SQL query
If the output of XML validation contains a large number of errors, you can use a Transact-SQL query to load the output in SQL Server Management Studio. Then you can analyze the error list with all the capabilities of the T-SQL language including WHERE, GROUP BY, ORDER BY, JOIN, and so forth.
DECLARE @xml XML;
SELECT @xml = XmlDoc
FROM OPENROWSET (BULK N'C:\Temp\XMLValidation_2016-02-212T10-41-00.xml', SINGLE_BLOB) AS Tab(XmlDoc);