I am trying to Raise an error using RAISEERROR function in sql when the XML schema validation fails.I am doing this inside a store proc. I am declaring the XML doc binding it to it's XSD schema in the TRY block and if validation fails, raise a user define error number by using RAISEERROR function in the catch block. However, somehow i am not able to do this and sql return it's own error the moment it executes that line of code. So using Employee example here, I am doing something like this.

CREATE PROC ImportEmployeeInfo@EmployeeXML as XMLAS

BEGIN

BEGIN TRY

DECLARE @EmpXML AS XML(EmployeeSchema) --declaring a local XML variable and binding it to a schemaDECLARE @LocalError

SET @EmpXML = @EmployeeXML -- here I am setting the @EmpXML to @EmployeeXML variable passed inIF @@error<>0 --if the @EmployeeXML failed the XSD validation, i beileve there would be an error rightBEGIN SET @LocalError = 50001 --user defined error RaiseError(' The input parameter @EmployeeXML is not valid', 16, 1)END

--The XML shredding and import into table goes here

END TRY

BEGIN CATCH

--I have another another user defined errorhandler proc here which will take the above @LocalError --as input parameter and raise a detailed error