Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

XML Workshop VII - Validating values with SCHEMA Expand / Collapse
Author
Message
Posted Thursday, April 2, 2009 10:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 9:15 PM
Points: 112, Visits: 319
Hi Jacob,

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 XML
AS

BEGIN

BEGIN TRY

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

SET @EmpXML = @EmployeeXML -- here I am setting the @EmpXML to @EmployeeXML variable passed in
IF @@error<>0 --if the @EmployeeXML failed the XSD validation, i beileve there would be an error right
BEGIN
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

END CATCH

END --End of Proc






Post #689575
Posted Tuesday, May 5, 2009 1:41 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Here is an example that I just tried and it works

DECLARE @x XML(CustomerSchema), @y XML
SELECT @y = '<test>a</test>'
BEGIN TRY
SELECT @x = @y
END TRY
BEGIN CATCH
RAISERROR('Hey, this is an invalid XML',16,1)
END CATCH
/*
OUTPUT:

Msg 50000, Level 16, State 1, Line 7
Hey, this is an invalid XML
*/



.
Post #709961
Posted Monday, July 25, 2011 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 5:40 AM
Points: 1, Visits: 30
hi, can any one please let me know how to get multiple error messages while validating xml using xsd?
no CLR or any dlls.
thank you so much in advance.
Post #1147542
Posted Monday, January 28, 2013 11:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 2:16 PM
Points: 1, Visits: 23
Is there any way to get all error messages validating xml using xsd?
no CLR or any dlls.
Post #1412590
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse