Filter on more than one XML element

Question

<Root>
    <Sub>
        <Name>a</Name>
        <Value>1</Value>
    </Sub>
    <Sub>
        <Name>b</Name>
        <Value>2</Value>
    </Sub>
</Root>

I know I can do a

SELECT * FROM table WHERE column.exist('/Root/Sub[Name="a" and Value="1"]') = 1

but how do I filter on both Name/Value pairs? a/1 and b/2?

Similar question answered here: http://stackoverflow.com/questions/6162273/search-for-multiple-values-in-xml-column-in-sql — alxK, Feb 07 '15 at 16:11

score 2 · Answer 1 · answered Feb 19 '13 at 20:30

2

How about:

SELECT * FROM table WHERE (column.exist('/Root/Sub[Name="a" and Value="1"]') = 1 OR (column.exist('/Root/Sub[Name="b" and Value="2"]') = 1)

Note the additional parentheses.

answered Feb 19 '13 at 20:30

jokeefe

1,836
4
22
27

score -1 · Answer 2 · edited Feb 10 '15 at 04:18

-1

This will work as expected:

SELECT *
  FROM TableName
 WHERE XMLColumnName.Exist('/Root/Sub[Id = "IdValue"][LimitType = "LimitTypeName"]') = 1

edited Feb 10 '15 at 04:18

Pang

9,564
146
81
122

answered Feb 10 '15 at 04:11

Venkatesan M

1

Filter on more than one XML element

2 Answers2