Jul 8, 2013

Xml data type methods are not supported in computed column definitions of table variables and return tables of table-valued functions. The error occurred at column "", table "", in the statement. Xml data type methods are not supported in computed column definitions. Create a scalar user-defined function to wrap the method invocation. The error occurred at column "", table "", in the CREATE TABLE statement.


We want to create a table which has a computed column which will keep the value form XML data.

CREATE TABLE  tblXML(
    XML_ID BIGINT IDENTITY,
    Data XML,
    Name AS Data.value('(/Root/Id)[1]', 'int' )
)

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


Xml data type methods are not supported in computed column definitions of table variables and return tables of table-valued functions. The error occurred at column "", table "", in the statement. Xml data type methods are not supported in computed column definitions. Create a scalar user-defined function to wrap the method invocation. The error occurred at column "", table "", in the CREATE TABLE statement.

Cause: we cannot use XML data type methods in computed columns.

Solution:

Instead of directly using the XML methods in computed column we can use user defined function. For example:

 CREATE TABLE  tblXML(
    XML_ID BIGINT IDENTITY,
    Data XML,
    Name AS dbo.GetName(Data)
)

Where GetName is user defined function:

CREATE FUNCTION GetName(
    @Data AS XML
)
RETURNS VARCHAR(100)
AS
BEGIN
   
    RETURN @Data.value('(/Root/Name)[1]', 'VARCHAR(100)' )
   
END

No comments:

Post a Comment