导入XML层次数据到SQL Server表的最佳方法 [英] Best way to import XML hierarchical data into Sql Server table

问题描述

我用ASP.NET编写C＃应用程序，其中用户可以在一个XML格式上传他的数据的Web应用程序。此数据具有一对多的关系。这是我选择了XML CSV的原因。如果有一种方法以CSV恳求做到这一点给出提示。它是冗长要少得多。
我的XML具有结构如下：

I have a web application written in ASP.NET C# application where user can upload his data in an XML form. This data has one-to many relationship. This is the reason I choose XML over CSV. If there is a way to do this with CSV pleas give a hint. It is much less verbose. My XML has following structure:

<Parent column1="value1" column2="value2">
    <Child column1="value3" />
    <Child column1="value4" />
</Parent>

这个模板重演。当我上传的文件是在字节数组（字节[]）的形式。我怎样才能优雅地导入两个相应的表格，父母与子女？
请记住，我将主持这个Web应用程序，所以我可能不会有存取权限的命令行工具或权利运行xp_cmdshell。
我现在的想法是创建System.XmlDocument然后遍历该文档，并通过存储过程插入行。对于每个表一个语句的每一行。
我是问：有没有更好的办法

This template repeats itself. When I get the uploaded file it is in a form of byte array (byte[]). How can I elegantly import that in the two corresponding tables, Parent and Child? Bear in mind that I will host this web app, so I probably will not have acces to command line tools or rights to run xp_cmdshell. My idea for now is to create System.XmlDocument and then traverse that document and insert rows via stored procedures. For each row in each table one statement. What I am asking is: Is there a better way?

推荐答案

您应该看一看SQLXML批量加载它导入大型XML文件进行了优化。我的数千条记录成功地使用这种在过去和击败其他XML进口手了。

You should have a look at SQLXML Bulk Load which is optimized for importing large xml files. I've used this successfully in the past with thousands of records and beats other xml imports hands down.

http://msdn.microsoft.com/en-gb/library/ ms171878.aspx

这篇关于导入XML层次数据到SQL Server表的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！