XPath gets attribute "A" value based on attribute "B" in SQL Server xml

Question

I have this XML in SQL Server

<data> <add key="images" value="image/path/img.gif" /> <data> 

I want to select the value attribute for each "add" node with the key = "image"

What I have now:

 SELECT ID, Data from Items where Data.value('(//data/add[@key="images"]/@value)[1]', 'nvarchar') Like '%img%' 

Any suggestions?

Answer 1

Which works fine for you if you just specified a size for your nvarchar extracted from XML.

 SELECT ID, Data from Items where Data.value('(//data/add[@key="images"]/@value)[1]', 'nvarchar(100)') Like '%img%' 

Here I indicated 100 , you can set it to something more suitable for your situation. Without a size, the column will have a size of 1 .

Answer 2

Use this XPath expression :

 (//data /add [@key="images"] /@value [contains(.,"img")] ) [1]