5

I need to validate XML using XSD inside TSQL. To make this task semi automated I need to do this inside TSQL function.

To make validation I have to set value to XML and if it is not valid I will receive an error.

My problem is that inside the TSQL function I can not use TRY / CATCH. Is this the propper way and if it is even possible to do this?

  • 1
    Why does it have to be a function? Can't you do the check in a stored procedure using an out param for the return value. – Mikael Eriksson Jan 4 '16 at 17:59
  • I need to use that UDF inside queries to make quicker validation, @Mikael Eriksson. – Bogdan Bogdanov Jan 5 '16 at 5:57
  • Ok, so you have the XML in a table and you need to figure out what rows have valid XML? Perhaps it is better for you to do that only once for each XML document when you add the XML to the table and store the result of the validation in a separate column. The operation of validating XML against a schema is expensive so you don't want to do that more than necessary. Use a cursor to loop through the table once for existing data and then apply the rule for every modification thereafter. Perhaps in a trigger. – Mikael Eriksson Jan 5 '16 at 6:08
  • In general I agree with you, @Mikael Eriksson. I need UDF because we will have engine for processing that XML. The XMLs will have very different set of parameters from each other. So the main idea is to have field with validation function and to compose stored procedures automatically (not each time of course, but after a general configuration). This is the reason for validation function. – Bogdan Bogdanov Jan 5 '16 at 14:03
2

One option (possibly your only option if doing this within the context of a scalar function is a hard requirement) is to use SQLCLR. SQLCLR scalar functions can do try / catch / finally structures within the C# or VB.NET code. So, you could pass in both the XML document and the XSD, both as the SqlXml type, and do the validation there. Just return a SqlBoolean for whether or not it validated.

Be sure to do the following so that the SQLCLR function can participate in parallel plans:

  • specify IsDeterministic = true in the SqlFunction attribute.
  • make sure that the function is in an Assembly that is marked as WITH PERMISSION_SET = SAFE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.