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);

Mikael Eriksson · Accepted Answer · 2012-02-02 14:29:22Z

3

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';

answered Feb 2, 2012 at 14:29

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Teto Over a year ago

thanks! legend. I was getting an error with this solution before, must have been the fact that I had @ in the variable

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related