validation of xml_data column in a table against a registered schema

Nov 20, 2014 at 12:53 PM
Hi, I have been trying to do validation of xml_data column against a schema at the database level for each row by using Try and Catch blocks. It has been extremely inefficient for large sets of data. If I were to ask a .NET developer to generate an assembly using your solution and registered it at the database level for using it as a batch validation program, would it work?

Nov 21, 2014 at 3:02 PM
Hi Sudhakar.

You could do, but I'm not sure how efficient it would be.
How many rows are you talking about?
database operations normally operate against sets, so they're very efficient at processing many rows at once.

You could do it by taking the Schema Validation code from XmlViewer and incorporating it into a CLR Stored procedure. Alternatively, you could read the data out of the database and then write some code to read the data from a dataset and operate on it in parallel. It depends whether you want to do this regularly or just as a once off, and how much data you're talking about.

Personally, I'd use a Insert Stored procedure which is a CLR procedure, and validate the XML on insert, and have a separate CLR Stored Procedure that you could use as a one off to validate the existing rows.

But in either case you wouldn't want to use the XmlViewer.exe program, but rather the source code that does the validation inside (or follow Microsoft's examples for using the XmlSchema object to do validation).

Hope this helps!

Daniel Probert.