如何在sql server存储过程中循环解析xml参数 [英] How to loop and parse xml parameter in sql server stored procedure

问题描述

我想编写一个接受 XML 参数的存储过程，解析它的元素并将它们插入到表中.这是我的 XML:

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:

我想在该参数中循环(例如 C# 中的 foreach)，检索每个人，然后解析它的数据(ID、NAME、LASTNAME)并将它们插入具有 3 个字段的表中.

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.

怎么做?

推荐答案

试试这个声明:

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.

当然，您可以将其扩展为 INSERT 语句的第二部分:

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

这篇关于如何在sql server存储过程中循环解析xml参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！