insert each xml node to sql server 2008 table

Question

I'm having xml as shown below :

<Production>
  <AIS_ID>ais150Dz</AIS_ID>
  <WorkedOn>12-12-2017</WorkedOn>
  <Status>Completed</Status>
</Production>

I want to insert this into table as shown below

ID   Field_Ident   Value_Ident
1     AIS_ID        ais150Dz
2     WorkedOn      12-12-2017
3     Status        Completed

I found suggestions to do this,but in that i have to manually declare column names while inserting the data e.g. "/Production/AIS_ID". I want to make it dynamic, that is regardless of how many nodes in XML. The reason is, returned xml is not fixed.

Any suggestions please.

Answer 1

Try this:

DECLARE @XML XML;

SET @XML = '<Production>
  <AIS_ID>ais150Dz</AIS_ID>
  <WorkedOn>12-12-2017</WorkedOn>
  <Status>Completed</Status>
</Production>'

SELECT  T.c.value('local-name(.)', 'VARCHAR(50)')
       ,T.c.value('(./node())[1]', 'VARCHAR(100)')
FROM @XML.nodes('Production/*')T(c);