C# 将数据从 XML 文件导入 SQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10795787/
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
Importing data from XML file to SQL database
提问by NDraskovic
Is it possible to import data from XML file to a SQL database, and if yes, how would that be done. I have a XML file that contains about 50 000 entries and I have to make an application that would manipulate that data (mostly reading and comparing) - so my concern is that the manipulation with that amount of data (and there is a very likely possibility that in the future there will be even more) would be very slow and inefficient. If there is some other option that you think would be better, please advise. Thanks
是否可以将数据从 XML 文件导入 SQL 数据库，如果是，将如何完成。我有一个包含大约 50 000 个条目的 XML 文件，我必须制作一个应用程序来操作该数据（主要是读取和比较）-所以我担心的是，使用该数量的数据进行操作（并且很有可能将来会有更多）将非常缓慢和低效。如果您认为还有其他更好的选择，请提供建议。谢谢
采纳答案by Habib
You can use SQL Server Import and Export Wizard. You can also look in SQL Server Integration Services. If you want to use C# then SQL server does support XML data type. You can make use of that.
您可以使用SQL Server 导入和导出向导。您还可以查看SQL Server 集成服务。如果您想使用 C#，那么 SQL Server 确实支持 XML 数据类型。你可以利用它。
You can also try to read the data in data set and then use BulkInsertto insert data in SQL Server
也可以尝试读取数据集中的数据，然后在SQL Server中使用BulkInsert插入数据
DataSet reportData = new DataSet();
reportData.ReadXml(Server.MapPath("yourfile.xml"));
SqlConnection connection = new SqlConnection("DB ConnectionSTring");
SqlBulkCopy sbc = new SqlBulkCopy(connection);
sbc.DestinationTableName = "yourXMLTable";
EDIT:For SQL Server 2005 check SQL Server 2005 Import / Export Wizard
编辑：对于 SQL Server 2005 检查SQL Server 2005 导入/导出向导
回答by Remco
Try looking at the SSIS tooling from microsoft Sql Server Intergration Services
尝试查看来自 microsoft Sql Server Intergration Services 的 SSIS 工具
http://msdn.microsoft.com/en-us/library/ms141026.aspx
http://msdn.microsoft.com/en-us/library/ms141026.aspx
With this tooling you can create save and run import packages, with filtering and custom selections . Possibly you can use this as a total solution for your problem.
使用此工具，您可以创建保存和运行导入包，以及过滤和自定义选择。也许您可以将其用作问题的整体解决方案。
回答by Steve B
it greatly depends on the complexity of the xml files (one depth of objects versus nested objects, kind and size of data, etc.).
它在很大程度上取决于 xml 文件的复杂性（对象的深度与嵌套对象、数据的种类和大小等）。
While SSIS can be a solution if you have knowledge, a simple c# application can also do the job.
如果您有知识，SSIS 可以是一个解决方案，一个简单的 c# 应用程序也可以完成这项工作。
The application should :
申请应：
- define a persistance agnostig class representing your data
- read the xml to load a list of this class (pagination may be required on very heavy volumes
- push the data to the dabase using ado or linq to sql (Entity framework is possible but probably too much for a simple batch import
- 定义一个表示数据的持久性 agnostig 类
- 读取 xml 以加载此类的列表（在非常繁重的卷上可能需要分页
- 使用 ado 或 linq to sql 将数据推送到数据库（实体框架是可能的，但对于简单的批量导入来说可能太多了
An alternative way could also to use a simple powershell script that performs the same kind of job.
另一种方法是使用一个简单的 powershell 脚本来执行相同类型的工作。
回答by Arnab Dey
In situation I got requirement like read data from xml file and send that xml file as parameter to store procedure Useful link http://www.aspdotnet-suresh.com/2012/12/aspnet-send-xml-file-as-parameter-to.html
在这种情况下，我需要从 xml 文件中读取数据并将该 xml 文件作为参数发送到存储过程有用的链接 http://www.aspdotnet-suresh.com/2012/12/aspnet-send-xml-file-as-parameter -to.html
回答by RAVI KAIPA
回答by Juan M. Elosegui
Another option to consider is:
另一个需要考虑的选择是：
Given the following xml
鉴于以下xml
<?xml version="1.0" standalone="yes" ?>
<Clients>
<Client>
<Id>1</Id>
<FirstName>FirstName1</FirstName>
<LastName>LastName1</LastName>
</Client>
<Client>
<Id>2</Id>
<FirstName>FirstName2</FirstName>
<LastName>LastName2</LastName>
</Client>
</Clients>
You could use the following query to select the content.
您可以使用以下查询来选择内容。
select
x.a.query('Id').value('.', 'int') as Id
, x.a.query('FirstName').value('.', 'nvarchar(50)') as FirstName
, x.a.query('LastName').value('.', 'nvarchar(50)') as LastName
from
(
select CAST(x AS XML) from OPENROWSET
(BULK 'C:\Users\jmelosegui\Desktop\Clients.xml', SINGLE_BLOB) as T(x)
) T(x)
cross Apply x.nodes('Clients/Client') as x(a)
Once that is done you could join with other tables or do your match after insert the data, and all from sql server side.
完成后，您可以加入其他表或在插入数据后进行匹配，所有这些都来自 sql server 端。
