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

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

|improve this answer|||||
  • @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
  • @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
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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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