XPath get value of attribute 'A' 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 of every "add" node with key = "images"

What i have now is:

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

Any suggestions?

Mikael Eriksson · Accepted Answer · 2012-01-09 12:48:57Z

4

What you have works fine if you just specify a size for your nvarchar retrieved from the XML.

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

Here I have specified 100 you may set it to something more appropriate for your situation. Without the size the column will have size 1.

answered Jan 9, 2012 at 12:48

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dimitre Novatchev · Accepted Answer · 2012-01-09 13:44:47Z

2

Use this XPath expression:

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

answered Jan 9, 2012 at 13:44

Dimitre Novatchev

244k27 gold badges307 silver badges438 bronze badges