title: SQL Server: How to get hold of XML attributes in a query?
body: <p>I'm currently trying to get all of the attributes from some XML with an SQL query.</p>
  
  <p>I've been attempting the following to retrieve it, but I must be missing something rather fundamental.</p>
  
  <pre><code>DECLARE @T varchar(max) 
  SET @T = 
  '&lt;root&gt;
      &lt;Field FieldRowId="1000"&gt;
      &lt;Items&gt;
          &lt;Item Name="CODE"/&gt;
          &lt;Item Name="DATE"/&gt;
      &lt;/Items&gt;
          &lt;Attributes&gt;
          &lt;Attribute ID ="1"/&gt;
          &lt;/Attributes&gt;
      &lt;/Field&gt;
      &lt;Field FieldRowId="2000"&gt;
      &lt;Items&gt;
                  &lt;Item Name="CODE"/&gt;
                  &lt;Item Name="DATE"/&gt;
      &lt;/Items&gt;
      &lt;Attributes&gt;
          &lt;Attribute ID ="2"/&gt;
      &lt;/Attributes&gt;
      &lt;/Field&gt;
  &lt;/root&gt;'
  
  DECLARE @X xml
  
  SET @X = CAST(@T as xml)
  SELECT Y.ID.value('@FieldRowId', 'int') as FieldID, 
     Y.ID.value('/Items/@Name', 'varchar(max)') as "Name",
     Y.ID.value('/Attributes/@ID', 'int') as AttributeID
  FROM @X.nodes('/root/Field') as Y(ID)
  </code></pre>