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

Question

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!!

Mikael Eriksson · Accepted Answer · 2011-10-05 13:23:46Z

1

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

edited Oct 5, 2011 at 13:23

answered Oct 5, 2011 at 13:08

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related