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?

Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306
Bogdan Bogdanov
  • 1,163
  • 2
  • 18
  • 38

1 Answers1

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.
Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306