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 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 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 S...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
CHEMA 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:
[IMAGE]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 (including SQL Server) as well as business intelligence design and implementation. He is also the author of the novel Dancing the River Lightly. You can find more information at www.rhsheldon.com.
