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?
Best Answer
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 aSqlBoolean
for whether or not it validated.Be sure to do the following so that the SQLCLR function can participate in parallel plans:
IsDeterministic = true
in theSqlFunction
attribute.WITH PERMISSION_SET = SAFE
.