How to retrieve value of attribute in xml datatype based on parameter name in SQL Server 2008 R2?

Question

I have the following sample xml in a column of xml data type:

 <diagnostics CurrentIterationNumber="79" TotalItemsProcessed="37847"
 ItemsProcessedLastIteration="75" ItemsProcessedPerIteration="479" />

I want to have a stored proc where I am able to pass in the name of the attribute to query. I then want to store its value into a variable to perform another set of calculations.

I have read that the .value function expects a string literal and many resources on the web advise to use the sql:variable() function for this. However, I am left with the code returning the actual contents of the variable.

Trying something along the following lines:

declare @property as varchar(50);
set @property = '@CurrentIterationNumber';

declare @x as xml;
set @x = (select PropertyValues from dbo.tblDiagnosticsSnapshot);
SELECT
    t.c.value('sql:variable("@property")', 'varchar(50)')
FROM
    @x.nodes('/diagnostics') t(c);

Answer 1

You can use /diagnostics/@* to get a list of attributes and the compare with local-name() to get the one you want.

declare @x xml 
set @x = '<diagnostics CurrentIterationNumber="79" TotalItemsProcessed="37847"
 ItemsProcessedLastIteration="75" ItemsProcessedPerIteration="479" />'

declare @property as varchar(50);
set @property = 'CurrentIterationNumber';

select @x.value('(/diagnostics/@*[local-name() = sql:variable("@property")])[1]', 'varchar(50)')

Or like this if you expect a list of values instead of only one as in your sample XML.

select t.c.value('.', 'varchar(50)')
from @x.nodes('/diagnostics/@*[local-name() = sql:variable("@property")]') as t(c)

Note: There should not be a @ in the search string. set @property = 'CurrentIterationNumber';