How to read an attribute of the XML.

Question

DECLARE @XmlData xml
SET @XmlData= '<?xml version="1.0" encoding="utf-8" ?>
<ROOT PROCESS_DATE="25-Nov-2009" ROW_COUNT="2" VERIFY_TOTAL="654454.54">
    <row rowNumber="1">
     <Code1>11111</Code1>
     <Code2>AAAA </Code2>
    </row>
    <row rowNumber="2">
     <Code1>2222</Code1>
     <Code2>BBBB </Code2>
    </row>
</ROOT>'

-- Following query returns the Code1, & Code2 for each row.  
SELECT 
       R.i.query('Code1').value('.', 'nvarchar(8)') AS Code1,
       R.i.query('Code2').value('.', 'nvarchar(8)') AS Code2
FROM @XmlData.nodes('/ROOT/row') R(i)

-- Now I want to get the rowNumber attribute of each row in the resultset.
SELECT 
       R.i.query('@rowNumber') AS rowNumber,
       R.i.query('Code1').value('.', 'nvarchar(8)') AS Code1,
       R.i.query('Code2').value('.', 'nvarchar(8)') AS Code2
FROM @XmlData.nodes('/ROOT/row') R(i)

-- Above query returns error

Answer 1

I don't have SQL Server handy, but my instincts tell me to try:

R.i.query('.').value('@rowNumber', 'int') AS rowNumber

Answer 2

You can query the attribute with the data() workaround:

R.i.query('data(row/@Name)').value('.','int') AS rowNumber

Answer 3

DECLARE @x XML ;
SET @x = '<?xml version="1.0" encoding="utf-8" ?>
<ROOT PROCESS_DATE="25-Nov-2009" ROW_COUNT="2" VERIFY_TOTAL="654454.54">
    <row rowNumber="1">
        <Code1>11111</Code1>
        <Code2>AAAA </Code2>
    </row>
    <row rowNumber="2">
        <Code1>2222</Code1>
        <Code2>BBBB </Code2>
    </row>
</ROOT>' ;

SELECT  v.value('.', 'VARCHAR(20)') AS Name
FROM    @x.nodes('(/ROOT/row/@*)') x ( v )