Can I use attribute name to identify XML element?
The .value method must always return a single value so you normally wrap it in brackets and move the ordinal ( eg [1] ) to the end.  Here's an example:

DECLARE @xml AS XML = '&lt;DATA&gt;
  &lt;ROW RowNumber="1"&gt;
    &lt;COLUMN Name="ProdID" Value="1234" /&gt;
    &lt;COLUMN Name="AppID" Value="20144" /&gt;
    &lt;COLUMN Name="ConsentDesc" Value="My Description" /&gt;
    &lt;COLUMN Name="ConsentVer" Value="" /&gt;
    &lt;COLUMN Name="ConsentAcceptance" Value="6789" /&gt;
    &lt;COLUMN Name="ConsentType" Value="TermsAndConditions" /&gt;
    &lt;COLUMN Name="CreatedDate" Value="8/22/2014 3:04:29 PM" /&gt;
  &lt;/ROW&gt;
&lt;/DATA&gt;'

SELECT  m.c.value('@RowNumber', 'varchar(50)') AS RowNumber ,
        m.c.value('(COLUMN[@Name="ProdID"]/@Value)[1]', 'varchar(50)') AS [ProdID] ,
        m.c.value('(COLUMN[@Name="AppID"]/@Value)[1]', 'varchar(50)') AS [AppID] ,
        m.c.value('(COLUMN[@Name="ConsentDesc"]/@Value)[1]', 'varchar(50)') AS [ConsentDesc.] ,
        m.c.value('(COLUMN[@Name="ConsentVer"]/@Value)[1]', 'varchar(50)') AS [ConsentVer.] ,
        m.c.value('(COLUMN[@Name="ConsentAcceptance"]/@Value)[1]', 'varchar(50)') AS [ConsentAcceptance] ,
        m.c.value('(COLUMN[@Name="ConsentType"]/@Value)[1]', 'varchar(50)') AS [ConsentType] ,
        m.c.value('(COLUMN[@Name="CreatedDate"]/@Value)[1]', 'varchar(50)') AS [CreatedDate]
FROM    @xml.nodes('DATA/ROW') AS m ( c )