XML column list specific value
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 
'&lt;document&gt;
      &lt;item&gt;
        &lt;key&gt;
          &lt;string&gt;Michael Burry&lt;/string&gt;
        &lt;/key&gt;
        &lt;value&gt;
          &lt;string&gt;CDO&lt;/string&gt;
        &lt;/value&gt;
      &lt;/item&gt;
      &lt;item&gt;
        &lt;key&gt;
          &lt;string&gt;Adam Smith&lt;/string&gt;
        &lt;/key&gt;
        &lt;value&gt;
          &lt;string&gt;£20&lt;/string&gt;
        &lt;/value&gt;
      &lt;/item&gt;
      &lt;item&gt;
        &lt;key&gt;
          &lt;string&gt;World Cup 2018&lt;/string&gt;
        &lt;/key&gt;
        &lt;value&gt;
          &lt;string&gt;football&lt;/string&gt;
        &lt;/value&gt;
      &lt;/item&gt;......  
&lt;/document&gt;'


SELECT *, yt.yourXML.value('(document/item[key/string[.="World Cup 2018"]]/value/string/text())[1]', 'VARCHAR(50)') result
FROM @yourTable yt