XML column list specific value

Question

I have an xml column that looks like this:

<document>
 <item>
 <key>
 <string>Michael Burry</string>
 </key>
 <value>
 <string>CDO</string>
 </value>
 </item>
 <item>
 <key>
 <string>Adam Smith</string>
 </key>
 <value>
 <string>20ドル</string>
 </value>
 </item>
 <item>
 <key>
 <string>World Cup 2018</string>
 </key>
 <value>
 <string>football</string>
 </value>
 </item>......

Instead of listing the entire contents of the column, I want to instead list only the value of <string>football</string> when the first value is <string>World Cup 2018</string>.

Using value('(/document/item/key/string)[7]', 'nvarchar(max)') is not suitable as "World Cup 2018" can appear anywhere.

Answer 1

If you just want a single value then you can just use the value method of the XML datatype with a little bit of XPath that translates to:

Get me the first value of ...key/string which has a sibling element of ...value/string under the same item element with the value "football"

Something like this:

DECLARE @yourTable TABLE ( rowId INT IDENTITY PRIMARY KEY, yourXML XML )
INSERT INTO @yourTable ( yourXML )
SELECT 
'<document>
 <item>
 <key>
 <string>Michael Burry</string>
 </key>
 <value>
 <string>CDO</string>
 </value>
 </item>
 <item>
 <key>
 <string>Adam Smith</string>
 </key>
 <value>
 <string>20ドル</string>
 </value>
 </item>
 <item>
 <key>
 <string>World Cup 2018</string>
 </key>
 <value>
 <string>football</string>
 </value>
 </item>...... 
</document>'
SELECT *, yt.yourXML.value('(document/item[key/string[.="World Cup 2018"]]/value/string/text())[1]', 'VARCHAR(50)') result
FROM @yourTable yt

• Peter: I have been able to get your solution working for me, however what I asked for was "I want to instead list only the value of <string>football</string> when the first value is <string>World Cup 2018</string>" The change I just described gives me what i wanted
• Peter: I guess if you edit your answer I can give you the accepted answer

Answer 2

I hope I understand your request. The key is

where
 x.u.exist('key/string[text()="World Cup 2018"]') = 1

Here is the select :

 select
 x.u.value('(key/string/text())[1]','varchar(50)') as myKey
 ,x.u.value('(value/string/text())[1]','varchar(50)') as myValue
 from @xml_Text.nodes('document/item') as x(u)
 where
 x.u.exist('key/string[text()="World Cup 2018"]') = 1

output:

myKey myValue
World Cup 2018 football

dbfiddle

or old way:

SELECT
 a.myKey ,a.myValue
 from
 (
 select
 x.u.value('(key/string/text())[1]','varchar(50)') as myKey
 ,x.u.value('(value/string/text())[1]','varchar(50)') as myValue
 from @xml_Text.nodes('document/item') as x(u) 
 )a
 where a.myKey = 'World Cup 2018'

• Peter: Thanks for the answer though I can't get this to work. The from @xml_Text.nodes('document/item') as x(u) part has a syntax error when I replace @xml_Text with my column name