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?

Improve this question
4
  • 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. Commented Jan 4, 2016 at 17:59
  • I need to use that UDF inside queries to make quicker validation, @Mikael Eriksson. Commented Jan 5, 2016 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. Commented Jan 5, 2016 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. Commented Jan 5, 2016 at 14:03

1 Answer 1

Reset to default
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.
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.