TSQL - Parsing XML Help, searching for specific value
Can anyone help me with the correct TSQL to parse the following xml. Say i wanted to find the item with a value of "y" for the name element, but i want to get the value of of "value" element, which is "2" in the example below.
Declare @XML xml set @XML =' <Test> <items> <item> <name>x</name> <value>1</value> </item> <item> <name>y</name> <value>2</value> </item> </items> </Test>' --i am stuck here selecct @XML.value('Test/items/....")
Result would be "2" by searching for "y".
Is this possible?
can someone help with the syntax? thanks!
select @xml.value('((test/items/item)/value)', 'nvarchar(max)')
(test/items/item) -- find the second instance of an item tag under a test tag ((test/items/item)/value) -- then find the first instance of a value tag under that
The value function cannot be given any expression which can even in theory return more than one value. If in doubt put brackets around it like this (some-expression-here).
To find the value corresponding to a given name:
select @xml.value('((test/items/item)[name="y"]/value)', 'nvarchar(max)')
You may need to use XQuery (designed to query XML data) for this. TSQL supports a subset of it: