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.

wBob · Accepted Answer · 2018-06-05 09:57:18Z

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
– Peter, Commented Jun 5, 2018 at 9:53
I guess if you edit your answer I can give you the accepted answer — Peter
– Peter, Commented Jun 5, 2018 at 9:54

Sabin B · Accepted Answer · 2018-06-04 16:40:30Z

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'

answered Jun 4, 2018 at 16:40

Sabin B

4,6111 gold badge21 silver badges24 bronze badges

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
– Peter

2018-06-05 09:46:26 +00:00
Commented Jun 5, 2018 at 9:46

Add a comment |

Stack Exchange Network

XML column list specific value

2 Answers 2

Your Answer

Hot Network Questions

XML column list specific value

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions