Sep 15, 2013

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>')

No comments:

Post a Comment