Can I use attribute name to identify XML element? Rather than using the element index COLUMN[1]/@value below, is it possible to retrieve the column value by specifying an attribute name such as: COLUMN[@name=”Prod_ID”]/@value?  

When I tried, I got this error:


  Msg 2389, Level 16, State 1, Line 41
  XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'


My code:

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[1]/@Value', 'varchar(50)') AS [ProdID] ,
   m.c.value('COLUMN[2]/@Value', 'varchar(50)') AS [AppID] ,
   m.c.value('COLUMN[3]/@Value', 'varchar(50)') AS [ConsentDesc.] ,
   m.c.value('COLUMN[4]/@Value', 'varchar(50)') AS [ConsentVer.] ,
   m.c.value('COLUMN[5]/@Value', 'varchar(50)') AS [ConsentAcceptance] ,
   m.c.value('COLUMN[6]/@Value', 'varchar(50)') AS [ConsentType] ,
   m.c.value('COLUMN[7]/@Value', 'varchar(50)') AS [CreatedDate]
FROM    
   @xml.nodes('DATA/ROW') AS m ( c )
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 )