将 xml 发送到 sql [英] send xml to sql

问题描述

有人可以提供一个示例，说明如何将 xml 从 C# 发送到 SQL Server，然后在获取它的存储过程中循环 xml，并逐行更新或输入.

Can someone provide an example of how to send an xml from C# to SQL Server, and then loop on the xml in the stored procedure that got it, and update or enter line by line.

推荐答案

在 15seconds 上查看由三部分组成的 SQL XML 系列:http://www.15seconds.com/Issue/050803.htm.

Check out the three-part series on SQL XML on 15seconds: http://www.15seconds.com/Issue/050803.htm.

我个人会使用 SQL XQuery 函数将您的 XML 分解成碎片并将它们存储在 SQL Server 中.

I would personally use the SQL XQuery functions to shred apart your XML into bits and pieces and store those in SQL Server.

如果你有类似的东西:

<data>
  <person>
     <name>Jones</name>         
     <firstname>Peter</firstname>
  </person>
  <person>
     <name>Smith</name>         
     <firstname>Frank</firstname>
  </person>
<data>

你可以这样写:

SELECT
   Data.Person.value('(name)[1]', 'varchar(20)') as 'Name',
   Data.Person.value('(firstname)[1]', 'varchar(20)') as 'First Name'
FROM 
   @XmlVar.nodes('/data/person') As Data(Person)

所以基本上，.nodes 函数将您的 XML 分解成一个伪表"Data.Person - 每个 条目成为表格中的一行.

So basically, the .nodes function shreds your XML into a "pseudo-table" Data.Person - each <person> entry becomes one row in the table.

使用 .value() 函数，您可以从那些分解的 XML 节点中提取单个值.您现在有一堆 varchar(20) 字段，可以是例如插入到表格中.

With the .value() function, you can extract single values from those shredded XML nodes. You now have a bunch of varchar(20) fields, that can be e.g. inserted into a table.

如果您的 XML 相当小(几百个条目)，则此方法很有效.如果您有巨大的 XML 文件，您可能需要研究其他方法，例如 XML批量加载.

This method works well if your XML is fairly small (a few hundred entries). If you have huge XML files, you might want to investigate other methods, such as XML Bulkload.

这篇关于将 xml 发送到 sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！