如何在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屋!