How to loop and parse xml parameter in sql server stored procedure
Try this statement:
SELECT Pers.value('(ID)[1]', 'int') as 'ID', Pers.value('(Name)[1]', 'Varchar(50)') as 'Name', Pers.value('(LastName)[1]', 'varchar(50)') as 'LastName'FROM @YourXml.nodes('/Employees/Person') as EMP(Pers)This gives you a nice, row/column representation of that data.
And of course, you can extend that to be the second part in an INSERT statement:
INSERT INTO dbo.YourTargetTable(ID, Name, LastName) SELECT Pers.value('(ID)[1]', 'int') as 'ID', Pers.value('(Name)[1]', 'Varchar(50)') as 'Name', Pers.value('(LastName)[1]', 'varchar(50)') as 'LastName' FROM @YourXml.nodes('/Employees/Person') as EMP(Pers)Done - no loops or cursors or any awful stuff like that needed! :-)