How to use WHERE on attribute in XML column in Sql Server 2008

Question

This is probably simple and I just don't get the braces right, but since I have virtually no experience in querying SQL SERVER XML data, it's driving me insane.

I have a simple table with an XML typed column NoSqlField. This contains either NULL or

<root version="1.0">
  <entry key="mykey">1</entry>
</root>

I want all rows in the table that have a <entry key="mykey">1</entry> value in their NoSqlField column.

A (rather stupid as it uses .ToString()) LINQ query using

where h.NoSqlField.ToString().IndexOf("<entry key=\"mykey\">1</entry>") > -1

returns a result, so it's definitely there.

How can I run the same query in T-SQL? I have tried

SELECT * FROM mytable WHERE 
NoSqlField.value('(//entry[@key=mykey])[1]','varchar(1)') = '1'

in various variations (with and without slashes, full path,...) but never got a single returned row.

Answer 1

Solution 1: If you want to check if the NoSqlField column has an //root/entry element and this element contains 1 (inner text) then you could use this solution:

SET ANSI_WARNINGS ON;
GO

DECLARE @TestData TABLE (
    ID INT IDENTITY PRIMARY KEY,
    NoSqlField XML NULL
);

INSERT  @TestData  (NoSqlField)
SELECT  NULL 
UNION ALL
SELECT  '
<root version="1.0">
  <entry key="mykey">1</entry>
</root>'
UNION ALL
SELECT  '
<root version="1.0">
  <entry key="mykey" anotherkey="myanotherkey">1</entry>
</root>'
UNION ALL
SELECT  '
<root version="1.0">
  <entry key="anotherkey" key2="a">1</entry>
</root>'
UNION ALL
SELECT  '
<root version="1.0">
  <entry key="mykey" key3="3">22</entry>
</root>';

SELECT  * 
FROM    @TestData t
WHERE   t.NoSqlField.exist('//root/entry[@key="mykey"][text() = "1"]') = 1

Results:

ID NoSqlField
-- ---------------------------------------------------------------------------------
2  <root version="1.0"><entry key="mykey">1</entry></root>
3  <root version="1.0"><entry key="mykey" anotherkey="myanotherkey">1</entry></root>

Note 1: you can see that this solution allows (the existence of) other attributes (ex. anotherkey).

Solution 2: If you want a strict filter (the entry element with just one attribute: key) then you could use this query:

SELECT  *
FROM
(
        SELECT  * 
                ,t.NoSqlField.exist('//root/entry[@key="mykey"][text() = 1]') AS XmlExist
                ,t.NoSqlField.query('
                    let $list := //root/entry/@*
                    for $i in $list
                    where local-name($i) != "key"
                        return <ret value="1"/>
                ').exist('//ret') AS HasAnotherAttribute
        FROM    @TestData t
) src
WHERE   src.XmlExist = 1
AND     src.HasAnotherAttribute = 0

Results:

ID NoSqlField                                              XmlExist HasAnotherAttribute
-- ------------------------------------------------------- -------- -------------------
2  <root version="1.0"><entry key="mykey">1</entry></root> 1        0

Note 2: let $list := //root/entry/@* will build a list with all (@*) attributes from //root/entry elements.

Note 3: local-name()