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)', '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)', '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.