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!

Answers


Try this

 select @xml.value('((test/items/item)[2]/value)[1]', 'nvarchar(max)')

Breakdown:

(test/items/item)[2]  -- find the second instance of an item tag under a test tag

((test/items/item)[2]/value)[1] -- 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)[1].

To find the value corresponding to a given name:

 select @xml.value('((test/items/item)[name="y"][1]/value)[1]', 'nvarchar(max)')

You may need to use XQuery (designed to query XML data) for this. TSQL supports a subset of it:

http://msdn.microsoft.com/en-us/library/ms189075%28SQL.90%29.aspx


Need Your Help

R plot diamond and adjust its width and height

r graphics plot

I want to plot diamonds, using different values for width and height.