13

I want to write a stored procedure that accept an XML parameter, parsing it's elements and inserting them in a table. This is my XML:

My XML

I want to loop in that parameter(such as a foreach in C#), retrieving each person, then parsing it's data(ID,NAME,LASTNAME) inserting them in a table that has 3 fields.

How can do that?

wonea
  • 4,783
  • 17
  • 86
  • 139
Arian
  • 12,793
  • 66
  • 176
  • 300

1 Answers1

48

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Hi @marc_s, could you please explain this line: @YourXml.nodes('/Employees/Person') as EMP(Pers) in the above INSERT statement? Is there a MSDN page where I can learn about this syntax? Thank you. – Tom Jan 15 '15 at 23:38
  • @Tom: [MSDN : XQuery Language Reference](http://msdn.microsoft.com/en-us/library/ms189075.aspx) - it basically "shreds" the XML into a pseudo table of XML fragments - one for each XML element matched by that XPath expression (one "row" for each `` element under `` ) – marc_s Jan 16 '15 at 06:10