Unable to get xml records into temp table via openxml

Question

I have the following XML

<NewDataSet>
<data name="AC_Capacity_Creation_Notification.Text">
    <value>Activation successfull and sent for approval.</value>
    <comments>AC Capacity</comments>
</data>
<data name="AC_Capacity_Modification_Daily_Notification.Text">
   <value>Approved successfully..</value>
   <comments>AC Capacity</comments>
 </data>
 <data name="AC_Capacity_Modification_Schedule_Notification.Text">
   <value>Are you sure you want to close this form?</value>
   <comments>AC Capacity</comments>
   </data>
 </NewDataSet>

I want to get the data from this XML into temp table from where i can query further on it but am unable to do so

I have tried doing it this way:

  EXEC sp_xml_PrepareDocument @intHandle OUTPUT, @XML    

  SELECT * into #tmp FROM OPENXML(@intHandle, '/NewDataset/data',2)

  EXEC sp_xml_removedocument @intHandle   

  drop table #tmp 

but I am getting empty rows

Answer 1

This is how i done it

 SELECT T.N.value('@name', 'varchar(100)')name,
  T.N.value('value[1]', 'varchar(100)')value
 FROM @XML.nodes('/NewDataSet/data') AS T(N)

It works ..thanks all for ur answers.

Answer 2

Just use native XQuery - much easier!

DECLARE @Input XML = '<NewDataSet>
<data name="AC_Capacity_Creation_Notification.Text">
    <value>Activation successfull and sent for approval.</value>
    <comments>AC Capacity</comments>
</data>
<data name="AC_Capacity_Modification_Daily_Notification.Text">
   <value>Approved successfully..</value>
   <comments>AC Capacity</comments>
 </data>
 <data name="AC_Capacity_Modification_Schedule_Notification.Text">
   <value>Are you sure you want to close this form?</value>
   <comments>AC Capacity</comments>
   </data>
 </NewDataSet>'

SELECT
    VALUE = NDSData.value('(value)[1]', 'varchar(200)'),
    Comments = NDSData.value('(comments)[1]', 'varchar(200)')
INTO 
    #temp
FROM
    @Input.nodes('/NewDataSet/data') AS Tbl(NDSData) 

Gives me an output of: