Check whether one of the attributes contains given value or not

Question

I am using SQL Server 2012. I have the following code

    DECLARE @p varchar(4)
    SET @p = ext1

    DECLARE @x xml
    SET @x = '<labels defaultText = "Some text 2012">      
                <label text = "Some text1" />      
                <label text = "Some text2" />      
                <label text = "Some text3" />
              </labels>'

Please, help to write an xpath/xquery query to check whether defaultText value or one of the texts' values contain @p or not. In this example the result will be true , because value of the text in the first label contains ext1.

can you also add the sql query which you have tried so far? – ughai Apr 16 '15 at 11:24 — ughai, Apr 16 '15 at 11:24

score 1 · Accepted Answer · answered Apr 16 '15 at 11:41

you can use .nodes() to iterate through <label> and use .value() for value of root node attribute as well as for values of attribute of <label>. Something like this

DECLARE @p varchar(4)
SET @p = 'ext1'

DECLARE @x xml
SET @x = '<labels defaultText = "Some text 2012">      
    <label text = "Some text1" />      
    <label text = "Some text2" />      
    <label text = "Some text3" />
    </labels>'

DECLARE @check BIT = 0

SELECT TOP 1 @check  =  1
FROM @x.nodes('labels/label') as t(c)
WHERE  c.value('@text','VARCHAR(20)') LIKE '%' + @p +'%'
    OR @x.value('/labels[1]/@defaultText','VARCHAR(20)') LIKE  '%' + @p +'%'

SELECT @check

Check whether one of the attributes contains given value or not

1 Answers1