How to load data from XML with attribute to database in Sql Server 2005

I was trying to read data of concerned columns from XML given below and load into database.

My XML is like below:

<questions>
<question>
   <Field name="id">12</Field> 
  <Field name="lid">10</Field> 
  <Field name="text">Hello</Field> 
 </question>
 </questions>

I have table with columns id,lid and text , i want to load data from above xml into database. Try to use OpenXML but in vain.not able to read attribute.

I was using below query.

  DECLARE @doc AS INT   

  EXEC Sp_xml_preparedocument   
    @doc OUTPUT,   
    @queueData   

  INSERT INTO dbo.Questions   
        开发者_运维技巧      ([Id],[Lid],[Text])

  SELECT * FROM   OPENXML(@doc, '/questions/question/Field', 1)   
            WITH ( id      VARCHAR(20),   
                   lid  VARCHAR(20) ,   
                   text     VARCHAR(MAX)) 

i want to load data from above xml into database. Try to use OpenXML but in vain.not able to read attribute.

ANY Help appreciated!!

Thank you!!

---

Try this.

declare @xml xml = '
<questions>
  <question>
    <Field name="id">12</Field> 
    <Field name="lid">10</Field> 
    <Field name="text">Hello</Field> 
  </question>
</questions>
'

select N.value('(Field[@name="id"])[1]', 'varchar(20)'),
       N.value('(Field[@name="lid"])[1]', 'varchar(20)'),
       N.value('(Field[@name="text"])[1]', 'varchar(max)')
from @xml.nodes('/questions/question') as T(N)

Read more about it here: http://msdn.microsoft.com/en-us/library/ms190798.aspx