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