Indexed computed column for frequenty used XML values

If you query an XML element or attribute value frequently, you can promote the value into a column and create an index on the promoted column. This makes the code easier to read and queries faster.

As an example, you have a XML column called requestXML in the table Orders. Within the requestXML is a XPath element /Order/accountId which your application queries frequently. If you try to create a computed column for the element value as follow, SQL server will return the error message ‘Xml data type methods are not supported in computed column definitions. Create a scalar user-defined function to wrap the method invocation’.

ALTER TABLE dbo.Orders
  ADD accountId AS requestXML.value('(/Order/accountId)[1]', 'varchar(6)')

This is SQL server way of saying you need to create a function

create function fnGetAccountIdFromRequestXML(@request xml)
 returns varchar(6)
 with schemabinding
 begin
     declare @accountId varchar(6)
     select @accountId = @request.value('(/Order/accountId)[1]', 'varchar(6)')
     return @accountId 
 end

Don’t forget the ‘with schemabinding’ part if you want to create an index on the computed column. Now you are ready to create the computed column and then the index

ALTER TABLE dbo.Orders
ADD accountId AS dbo.fnGetAccountIdFromRequestXML(requestXML);

CREATE NONCLUSTERED INDEX IX_Orders_accountId  ON  dbo.Orders(accountId)

The accountId element in requestXML has now been promoted to an indexed computed column. It can now be used in queries just like any normal SQL database table columns.