Take the 2-minute tour ×
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. It's 100% free, no registration required.
declare @xml xml,
declare @num int
set @num = 1
set @xml = '
<row>
    <Id>1</Id>
    <name>reza</name>
</row>
<row>
    <Id>2</Id>
    <name>Masoud</name>
</row>
'

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
1  
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....... –  masoud afrashteh Dec 22 '12 at 13:52

2 Answers 2

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:

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

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

share|improve this answer

Your Answer

 
discard

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.