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!
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 @[email protected]('(/root/name)[1]','nvarchar(max)');
SET @age [email protected]('(/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 @[email protected]('(/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
@name and @age variables. I read it as: replace each occurance of <name> and <age> in a variable by the element's value in the XML. I could be wrong though.<name> or <age> and shuffle it into a variable. I took this Set @name='get value of node <name>' the way I put it above... We'll see :-)(/root/name)[1] etc? I mean less text :)
<name>and<age>in several strings, by the respective element values in an XML stored in a variable... Correct? Or, do you want to get the element values of any element in the XML, and substitute any occurance of those tags in those strings?