Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
declare @xml xml,
declare @num int
set @num = 1
set @xml = '

how to select ID = @num ??

share|improve this question
Doesn't replacing 1 with @num in marc_s's answer solve your problem? – Mat Dec 22 '12 at 12:54
Please don't edit your question to ask something different then unaccept valid answers to your previous question. select @xml.query('*[Id/text()=sql:variable("@num")]'); should work I think. – Martin Smith Dec 22 '12 at 12:54
thanks dear....... – Massoud Afrashteh Dec 22 '12 at 13:52
up vote 3 down vote accepted

Use XPath filters:

select @xml.query('*[Id/text()="1"]');

will show: <row><Id>1</Id><name>reza</name></row>. '/row[Id/text()="1"]' would also work for your example, as well as '/row[Id="1"]' or even '/row[Id=1]'.

share|improve this answer
or even (./row[Id[1]="1"])[1] :) – Paul White Dec 20 '12 at 12:26
@SQLKiwi: I think we should make a competition rule that one need to shorten the expression, not expanding it ;) – Remus Rusanu Dec 20 '12 at 13:05
Ha, yes :) Though, I was looking to make improvements to the execution plan with that suggestion. Also, there is a semantic difference in that it only returns the first node with Id=1. One might expect the Id to be unique, of course, but without a schema to enforce that, who knows. It might even be slightly more efficient to stop looking once the node is found - not sure. – Paul White Dec 21 '12 at 3:17
@SQLKiwi: yes, the ()[1] forces a singleton indeed (as we well know from what .value() is keep forcing us to do...) – Remus Rusanu Dec 21 '12 at 16:22

Try this:

    @xml.nodes('/row') AS XTbl(XRows)
    XRows.value('(Id)[1]', 'int') = 1

This will return the entire <row> XML element as XML

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.