Q:

Get the value of each XML node and set it to a parameter

How to set a parameter in each XML nodes using SQL Server 2008?
I am new with SQL and I have XML data like this
Set @xml='<root><name>John </name><age>8</age></root>'

I need to set the value of node <name>, <age> in a parameter. For example:
Set @name='get value of node <name>'
Set @age='the value of <age>'

Is this possible? Thanks!

A:

If I get this correctly you are getting parameters within an XML as key-value-pairs. What you try to achieve is to read the value into the fitting variable. Try it like this:
DECLARE @xml XML;
SET @xml='<root><name>John </name><age>8</age></root>';

DECLARE @name NVARCHAR(100);
DECLARE @age INT;

SET @name=@xml.value('(/root/name)[1]','nvarchar(max)');
SET @age =@xml.value('(/root/age)[1]','int');

SELECT @name,@age;

If you'd need to query the values from a variable name, you can use this:
DECLARE @nodeName NVARCHAR(100)='name';
SET @name=@xml.value('(/root/*[local-name()=sql:variable("@nodeName")])[1]','nvarchar(max)');

The central idea is to use an XQuery-expression (/root/*[local-name()=sql:variable("@nodeName")])[1] to pick the first node's value, where the element's name is like the given parameter 

