2

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.

3

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
  • 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 Jun 5 '18 at 9:53
  • I guess if you edit your answer I can give you the accepted answer – Peter Jun 5 '18 at 9:54
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'
  • 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 – Peter Jun 5 '18 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.