Using XQuery Expressions to Create Computed Columns

In my SQL Server 2005 database, I've created a table that includes an XML column. I want to use two of the XML column's element values to create a computed column in that table. What's the best approach for creating this type of computed column?

In SQL Server 2005, you can create a computed column by using XQuery expressions to extract values from specific elements, or levels, in the XML hierarchy. An XQuery expression is based on the XQuery expression language, which is designed to retrieve data from XML documents. SQL Server 2005 supports the use of XQuery expressions for accessing data in an XML column or an XML variable.

To use an XQuery expression to retrieve XML data (and subsequently create a computed column based on that data), you must invoke an XML data type method. The value method returns one value of the type you specify as one of the method's arguments. However, before you get into too much detail about the value method and the XQuery expression, you must first remember that SQL Server doesn't permit the use of XML methods in computed column definitions.

To get around this limitation, you must create a user-defined function (UDF) that contains the necessary XQuery expression, then call that function in your computed column definition. For example, suppose that you create the table that the code at callout A in Listing 1 shows. (The table is part of the np schema in the Funders database.) Notice that the NPDetails column is an XML data type column.

Now take a look at the INSERT statement at callout B in Listing 1. The INSERT statement adds values to the NPOrgName an NPDetails columns. The NPDetails value is structured XML that has the Details element at the root node; all other elements are children of the Details element. Two of the child elements are OrgsFundedAnnually and AmtFundedAnnually. In this example, I create a computed column that divides the AmtFundedAnnually value by the OrgsFundedAnnually value (the number of organizations granted money each year).

To create the computed column, I first define a function that contains the XQuery expression. Note that you don't have to create and populate the table before creating the function. You can first create the function, then create a table that contains the computed column. I've taken that approach in this example to illustrate how data is being extracted from the XML column.

Listing 2 shows the code that creates the function. The code at callout A in Listing 2 shows the definition for the AvgGrant function. The function's RETURN clause includes the XQuery expression that retrieves data from the XML column (through the @amt variable). To use the expression, you must invoke the XML value method. The value method takes two arguments: the XQuery expression and the data type of the returned value. You can break the XQuery expression into two parts separated by a semicolon. The first part is the namespace declaration (declare namespace ns="http://myschemas.com /funders"). The declaration assigns the alias ns to the namespace. If you refer back to callout B in Listing 1, you'll see that the namespace in the XQuery expression matches the namespace defined as part of the XML column data. If you don't specify a namespace in the XML column, SQL Server assumes that it should use the default XML namespace, in which case you don't include the namespace declaration in your XQuery expression.

If you do declare a namespace in the XQuery expression, as I've done in this example, you can reference the namespace through its alias in the second part of the expression at callout A in Listing 2. This code defines the location of the applicable values and what, if any, actions to take. For example, the first part of this callout (/ns:Details/ns:AmtFundedAnnually) identifies the AmtFundedAnnually element, which is a child of the Details element. Notice the use of the ns alias as part of the XML path definition. The second part of this section (/ns:Details/ns:OrgsFundedAnnually) specifies the OrgsFundedAnnually element, which is also a child of the Details element. The two path definitions, or path expressions, are separated by the div operator, which indicates that you should divide the first value by the second value. In other words, the function divides the AmtFundedAnnually value by the OrgsFundedAnnually value.

One other item you probably noticed in the XQuery expression is the [1] after each path expression. Because the value method must return only one value (you'll receive an error if multiple values are returned), you must specify [1] after each path to ensure that only one value is returned. This specification is required because a parent element can contain multiple identical child elements.

The second argument in the value method is the data type of the returned value. You can specify most T-SQL data types except XML, TIMESTAMP, or a user-defined type (UDT). In this example, I specify the numeric(10,2) type because I expect my expression to return a numeric value. Notice that this type is consistent with the one I specify in the RETURNS clause of the function definition. When you create your function and its XQuery expression, be sure that you're always returning values with the appropriate types.

Once you've defined your function, you can use it in a computed column definition like the one at callout B in Listing 2. You simply pass the name of the XML column as an argument to the AvgGrant function. The function applies the XQuery expression to the XML data and returns the calculated value. Then, you can retrieve data from the NonprofOrgs table (which the code at callout C shows), as you would any other table.

If you refer back to the INSERT statement at callout B in Listing 1, you see that the AmtFundedAnnually value is 12000, and the OrgsFundedAnnually value is 12. This means that the NPAmtPerOrg computed column will return a value of 1000 (12000/12).

As you can see, using a function is a fairly straightforward approach for creating a computed column based on XML data. The trick, of course, is to write an XQuery expression that retrieves exactly the information you want to retrieve and that computes the data accurately. As it's implemented in SQL Server 2005, XQuery includes a range of functions and operators that can support sophisticated expressions. For more information about XQuery and XML methods, refer to SQL Server 2005 Books Online (BOL). You can also learn more about XQuery at http://www.w3.org/XML/Query. The information in these resources is useful whether you're creating computed columns or retrieving data directly from XML variables and columns.