Xml data type methods are not supported in check constraints. Create a scalar user-defined function to wrap the method invocation. The error occurred at table "".

We want to create a table which has column of data type XML. We want to add one check constraint using XML data.

CREATE TABLE  tblXML(

     ID BIGINT IDENTITY,

     Data XML CHECK(Data.value('(/Root/Id)[1]', 'int' ) > 100)

)

Now if will execute above sql statement in sql server, we will get error message :

Xml data type methods are not supported in check constraints. Create a scalar user-defined function to wrap the method invocation. The error occurred at table "".

Cause: we cannot use XML data type methods in check constraint.

Solution:

Instead of directly using the XML methods in check constraint expression we can use user defined function. For example:

CREATE TABLE  tblXML(

     ID BIGINT IDENTITY,

     Data XML CHECK(dbo.CheckXML(Data) = 1)

)

Where CheckXML is user defined function:

CREATE FUNCTION CheckXML(

     @Data AS XML

)

RETURNS BIT

AS

BEGIN

     DECLARE @IsValid AS BIT

    

     IF @Data.value('(/Root/Id)[1]', 'int' ) > 100

          SET @IsValid = 1

     ELSE

          SET @IsValid = 0

         

     RETURN @IsValid

    

END

To test the constaint:

It will insert:

INSERT INTO tblXML VALUES('<Root><Id>500</Id><Name>Scott</Name><Location>USA</Location></Root>')

It will not insert:

INSERT INTO tblXML VALUES('<Root><Id>5</Id><Name>Scott</Name><Location>USA</Location></Root>')