xml 如何在sql server存储过程中循环解析xml参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5758091/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to loop and parse xml parameter in sql server stored procedure
提问by Arian
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:
我想编写一个接受 XML 参数的存储过程，解析它的元素并将它们插入到表中。这是我的 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.
我想在该参数中循环（例如 C# 中的 foreach），检索每个人，然后解析它的数据（ID、NAME、LASTNAME）并将它们插入具有 3 个字段的表中。
How can do that?
怎么能这样？
回答by marc_s
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 语句中的第二部分：
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! :-)
完成 - 不需要循环或游标或任何可怕的东西！:-)
