XML column list specific value I have an xml column that looks like this:

&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;......


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

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