Using XML in Computed Columns

Creating Computed Columns from XML Columns

In the following CREATE TABLE statement, an xml type column (col2) is computed from col1:

CREATE TABLE T(col1 varchar(max), col2 AS CAST(col1 AS xml) )  

The xml data type can also appear as a source in creating a computed column, as shown in the following CREATE TABLE statement:

CREATE TABLE T (col1 xml, col2 as cast(col1 as varchar(1000) )) 

You can create a computed column by extracting a value from an xml type column as shown in the following example. Because the xml data type methods cannot be used directly in creating computed columns, the example first defines a function (my_udf) that returns a value from an XML instance. The function wraps the value() method of the xml type. The function name is then specified in the CREATE TABLE statement for the computed column.

CREATE FUNCTION my_udf(@var xml) returns int
AS BEGIN 
RETURN @var.value('(/ProductDescription/@ProductModelID)[1]' , 'int')
END
GO
-- Use the function in CREATE TABLE.
CREATE TABLE T (col1 xml, col2 as dbo.my_udf(col1) )
GO
-- Try adding a row. 
INSERT INTO T values('<ProductDescription ProductModelID="1" />')
GO
-- Verify results.
SELECT col2, col1
FROM T
 

As in the previous example, the following example defines a function to return an xml type instance for a computed column. Inside the function, the query() method of the xml data type retrieves a value from an xml type parameter.

CREATE FUNCTION my_udf(@var xml) 
  RETURNS xml AS 
BEGIN 
   RETURN @var.query('ProductDescription/Features')
END

In the following CREATE TABLE statement, Col2 is a computed column that uses the XML data (<Features> element) that is returned by the function:

CREATE TABLE T (Col1 xml, Col2 as dbo.my_udf(Col1) )
-- Insert a row in table T.
INSERT INTO T VALUES('
<ProductDescription ProductModelID="1" >
  <Features>
    <Feature1>description</Feature1>
    <Feature2>description</Feature2>
  </Features>
</ProductDescription>')
-- Verify the results.
SELECT *
FROM T

In This Section

See Also

Other Resources