1
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 ??

2
  • Doesn't replacing 1 with @num in marc_s's answer solve your problem?
    – Mat
    Commented Dec 22, 2012 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. Commented Dec 22, 2012 at 12:54

2 Answers 2

3

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]'.

2
  • @SQLKiwi: I think we should make a competition rule that one need to shorten the expression, not expanding it ;) Commented Dec 20, 2012 at 13:05
  • @SQLKiwi: yes, the ()[1] forces a singleton indeed (as we well know from what .value() is keep forcing us to do...) Commented Dec 21, 2012 at 16:22
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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