Search code examples
sql-serversqlxmlxquery-sql

Selecting individual XML node with condition in SQL

I have a XML structure like

<root>
 <row>
  <value>1</value>
</row>
<row>
 <value>2</value>
</row>
</root>

I want to get <row> where <value> = 2. Is it possible? Any example?

To be more precise the xml structure is like this

<root>
 <row>
   <value>1</value>
 </row>
 <root>
   <row>
    <value>2</value>
   </row>
 </root>
</root>
Solution

  • Sure:

    declare @x xml = '<root>
 <row>
  <value>1</value>
</row>
<row>
 <value>2</value>
</row>
</root>';

select @x.query('/root[1]/row[./value/text()="2"]');