How to read an XML data in sql server?

Question

I have an XML

<Table>
  <Columns>
  <Column Name='Name' Datatype='varchar(100)'/>
  </Columns>
  <Rows>
  <Row Name='Test' Number='123'/>
  </Rows>
</Table>

I want to read Name value in the provided xml in sql server How Can I do that?

I tried using

`Declare @XMl XML 
select @xml= cast(Data as xml) from Table
select y.value('Name[1]','varchar(100)') as Name
@xml.nodes(//Row) as x(y)` 

but am getting null values as result set How can I acheieve this in sql server

Answer 1

Like this:

    declare @doc xml = 
    '<Table>
      <Columns>
      <Column Name="Name" Datatype="varchar(100)"/>
      </Columns>
      <Rows>
      <Row Name="Test" Number="123"/>
      </Rows>
    </Table>'
    
    select r.value('@Name', 'varchar(200)') Name
    from @doc.nodes('/Table/Rows/Row') n(r)