Create a computed column in SQL Server using XML data

In a SQL Server database, computed columns are useful when you want to persist data that is otherwise stored in a table as individual values. For example, you might have a table that includes columns that store dollar amounts, such as a column for wholesale prices and a column for retail prices. You might then decide that, rather than calculating the difference between those two columns each time you query the table, you want to store that value in a third column that automatically calculates the difference between the two amounts. This column is the computed column.

To create a computed column in SQL Server using XML data, your column definition must include the expression necessary to determine what value to insert into the column. For instance, in the example

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at [email protected].

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in June 2008

above, your expression would subtract the value in the wholesale column from the value in the retail column. That difference would then be automatically inserted into the computed column whenever you add or update a row in that table.

You can easily create a computed column on two or more columns that contain string or numerical values. (For details on how to create this type of computed column, see Microsoft SQL

More tips on using T-SQL in SQL Server 2005:

 Server Books Online.) However, if you want to create a computed column based on specific element values within an XML column, the process gets a bit more complicated. This is because you must use an XQuery expression to retrieve specific element data from an XML column, and SQL Server does not support the use of XQuery expressions in a computed column definition.

To get around this issue, you can create a function to retrieve the XML data that you want to include in your computed column and then call that function within your computed column definition. To better demonstrate how this works, let's take a look at an example. Here, I created the following schema and table in the AdventureWorks sample database in SQL Server 2005:

USE AdventureWorks;
GO
CREATE SCHEMA hr
GO
SELECT TOP 10 JobCandidateID AS CandidateID,
[Resume] AS JobResume
INTO hr.CandidateNames
FROM HumanResources.JobCandidate
GO

As the name suggests, the Resume column in the HumanResources.JobCandidate table is an XML column containing the candidate's resume. I'm extracting data from this table to create the CandidateNames table in the hr schema. (I create a separate table because I want to be able to modify the table definition in order to add the computed column.)

After you've set up your test environment, you can create the function. The function should include the XQuery expression necessary to retrieve data from a specific XML column. For example, the following function retrieves the first and last names of the job candidate, as those names are stored in the JobResume column:

CREATE FUNCTION hr.FullName (@name XML)
RETURNS NVARCHAR(60) AS
BEGIN
RETURN @name.value('declare namespace ns=
"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
concat((/ns:Resume/ns:Name/ns:Name.First)[1], " ",
(/ns:Resume/ns:Name/ns:Name.Last)[1])','nvarchar(60)')
END

As you can see, the FullName function takes one input parameter, which is defined as type XML. The idea is that when you call this function, you will use as an input value the name of the XML column that contains the data you want to extract. Because you're extracting XML data, you can use the value() method, which is available to the XML data type.

The value() method takes two arguments. The first argument defines the namespace that is used in the target XML column, and the second argument contains the XQuery expression that retrieves the actual data. In this case, the expression uses the concat() method to concatenate the first and last names, as they appear in the XML document. Refer to my tip, "Retrieve XML data values with XQuery" for more information about how to use the value() method and create an XQuery expression.

Once your function is created, you can test it by retrieving data from the JobResume column in the hr.CandidateNames table:

SELECT CandidateID, hr.FullName(JobResume) AS FullName
FROM hr.CandidateNames

As you can see, I've passed in the name of the XML column as an argument to the FullName function. This SELECT statement should return the following results:

CandidateID FullName
1 Shai Bassli
2 Max Benson
3 Krishna Sunkammurali
4 Stephen Jiang
5 Thierry D'Hers
6 Christian Kleinerman
7 Lionel Penuchot
8 Peng Wu
9 Shengda Yang
10 Tai Yee

(10 row(s) affected)

Notice that the results include the first and last names as they appear in the XML column. If you refer back to the function definition, you'll see that the XQuery expression used in the value() method specifies that the expression return the value as NVARCHAR(60) to accommodate Unicode characters, such as those in the last three rows of the query results.

Once you've tested your function, you're ready to create the computed column: The following ALTER TABLE statement adds the FullName column to the CandidateNames table:

ALTER TABLE hr.CandidateNames
ADD FullName AS hr.FullName(JobResume)

I've used the FullName function in the computed column expression and passed in the JobResume column as an argument to the function. After you run the ALTER TABLE statement, you can test that the data has been inserted into the computed column by running the following SELECT statement:

SELECT CandidateID, FullName FROM hr.CandidateNames

When you run this statement, you should receive the same results as those shown above.

That's all there is to creating a computed column in SQL Server based on XML data. The key is to create a function that runs the XQuery expression and then use that function within your computed column definition. For more details on computed columns, XML columns and XQuery expressions, see Microsoft SQL Server Books Online.

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at  http://www.rhsheldon.com.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top