SQL Server: How to get hold of XML attributes in a query?

Question

I'm currently trying to get all of the attributes from some XML with an SQL query.

I've been attempting the following to retrieve it, but I must be missing something rather fundamental.

DECLARE @T varchar(max) 
SET @T = 
'<root>
    <Field FieldRowId="1000">
 <Items>
  <Item Name="CODE"/>
  <Item Name="DATE"/>
 </Items>
     <Attributes>
  <Attribute ID ="1"/>
     </Attributes>
    </Field>
    <Field FieldRowId="2000">
 <Items>
                <Item Name="CODE"/>
                <Item Name="DATE"/>
 </Items>
 <Attributes>
  <Attribute ID ="2"/>
 </Attributes>
    </Field>
</root>'

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)

Answer 1

You would have to try something like this: (the @name attribute is on the "Item" element - not the "Items" !)

SET @X = CAST(@T as xml)
SELECT 
   Y.ID.value('(@FieldRowId)[1]', 'int') as FieldID,
   Y.ID.value('(Items/Item/@Name)[1]', 'varchar(max)') as "Name",
   Y.ID.value('(Attributes/Attribute/@ID)[1]', 'int') as AttributeID
FROM @X.nodes('/root/Field') as Y(ID)

Marc