Selecting individual XML node with condition in SQL

Question

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>

Your updated xml structure is not well formated...and you can also use @x.nodes('/roots/root/row') a(b) if i assume there is a roots of all root — Krishnraj Rana
– Krishnraj Rana, Commented Oct 8, 2014 at 11:19
This is wired xml structure. Your first node <row> is directly under the root and your second <row> is under the <root> of root. — Krishnraj Rana
– Krishnraj Rana, Commented Oct 8, 2014 at 11:31

Roger Wolf · Accepted Answer · 2014-10-08 10:43:23Z

4

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"]');

answered Oct 8, 2014 at 10:43

Roger Wolf

7,7872 gold badges29 silver badges37 bronze badges

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

5 Comments

Garima Over a year ago

Thanks Roger. Could you also please explain it. Why root[1]?

Roger Wolf Over a year ago

@Garima, "1" in square brackets means "first instance of element". It makes no difference in your example, but you can get somewhat better performance with bigger XML.

Garima Over a year ago

Thanks @Roger. My mistake I made the wrong xml. Updated my question with the right one. Could you answer now. Sorry I am a newbie in this

Roger Wolf Over a year ago

@Garima, it isn't a well-formed XML (both aren't, actually). Each opened node has to be closed. In the first sample, the root node has no closing tag. The same is true in the second.

Roger Wolf Over a year ago

@Garima, that's worse, since you have your nodes of interest on different levels. You can employ "search everywhere" mode, but it can be very costly: select @x.query('//row[data(./value)="2"]');

Collectives™ on Stack Overflow

Selecting individual XML node with condition in SQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related