SQL使用存储过程和xml参数插入多行? [英] SQL Insert multiple rows using stored procedure and xml parameter?
本文介绍了SQL使用存储过程和xml参数插入多行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我以前使用过表值参数,但我不确定如何使用 xml.
I've used a table valued parameter before, but I'm not sure how to use xml.
我不知道格式化我的 xml 的最佳方法,但我想我会试试这个:
I do not know the best way to format my xml but I think I would try this:
<Car>
<Name>BMW</Name>
<Color>Red</Color>
</Car>
然后我会将 xml(一辆或多辆汽车)传递给存储过程,它会为我通过的每辆车插入一行(名称列在名称列中等等.)
Then I would pass the xml (one or more car) to the stored procedure and it would insert one row for each car I pass (with the name going in a name column etc..)
有谁知道如何编写存储过程?(我通常会自己尝试,但我没有太多时间测试T_T)
Does anyone know how to write the stored procedure? (I'd usually try it myself but I don't have much time for testing T_T)
推荐答案
您可以使用节点功能分解 XML:
You can shred the XML using the nodes function:
CREATE PROC ShredXML (@x xml)
AS BEGIN
INSERT INTO TBL_TARGET (Name, Color)
SELECT
x.y.value( 'Name[1]', 'VARCHAR(20)' ) AS Name,
x.y.value( 'Color[1]', 'VARCHAR(20)' ) AS Color
FROM @x.nodes('cars/car') x(y)
END
exec ShredXML @x = N'<cars><car><Name>BMW</Name><Color>Red</Color></car><car><Name>Audi</Name><Color>Green</Color></car></cars>'
这篇关于SQL使用存储过程和xml参数插入多行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文