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