how to select * from XML where id=1?

Question

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

Answer 1

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

Answer 2

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