什么是XML数据保存到SQL Server的最佳方式是什么？ [英] What is the best way to save XML data to SQL Server?

问题描述

有一个直接的路线是pretty的直线前进？ （即可以在SQL Server中读取XML）

Is there a direct route that is pretty straight forward? (i.e. can SQL Server read XML)

或者说，它是最好的解析XML，只是通过ADO.Net无论是作为独立的行或者批量更新？

Or, is it best to parse the XML and just transfer it in the usual way via ADO.Net either as individual rows or perhaps a batch update?

我知道有可能是涉及大量复杂的存储解决方案特效 - 虽然我不完全反对这一点，我倾向于preFER有我的大部分业务逻辑在C＃code。我已经看到了使用SQLXMLBulkLoad一个解决方案，但它似乎需要相当复杂的SQL code。

I realize there may be solutions that involve large complex stored procs--while I'm not entirely opposed to this, I tend to prefer to have most of my business logic in the C# code. I have seen a solution using SQLXMLBulkLoad, but it seemed to require fairly complex SQL code.

作为参考，我将用大约100行工作的时间大约有50小块数据的每一个（字符串和整数）。这将最终成为日常批处理作业。

For reference, I'll be working with about 100 rows at a time with about 50 small pieces of data for each (strings and ints). This will eventually become a daily batch job.

任何code片段，您可以提供将非常AP preciated。

Any code snippets you can provide would be very much appreciated.

推荐答案

SQL Server 2005和起来有个数据类型叫XML你可以在存储XML - 分型或输入一个XSD架构

SQL Server 2005 and up have a datatype called "XML" which you can store XML in - untyped or typed with a XSD schema.

您可以填写基本XML类型的列从XML文本字符串，这样你就可以很容易地只是使用普通的INSERT语句，并填补了XML内容到该字段。

You can basically fill columns of type XML from an XML literal string, so you can easily just use a normal INSERT statement and fill the XML contents into that field.

马克·

这篇关于什么是XML数据保存到SQL Server的最佳方式是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！