Is there a direct route that is pretty straight forward? (i.e. can SQL Server read XML)
有直接的路线是非常直接的吗？ （即SQL Server可以读取XML）
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?
或者，最好是解析XML并通过ADO.Net以通常的方式将其作为单独的行或者批量更新进行传输？
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.
我意识到可能存在涉及大型复杂存储过程的解决方案 - 虽然我并不完全反对这一点，但我倾向于在C＃代码中拥有大部分业务逻辑。我见过使用SQLXMLBulkLoad的解决方案，但它似乎需要相当复杂的SQL代码。
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.
作为参考，我将一次处理大约100行，每行约50个小数据（字符串和整数）。这最终将成为每日批量工作。
Any code snippets you can provide would be very much appreciated.
您可以提供的任何代码段都非常感谢。
5 个解决方案
#1
10
SQL Server 2005 and up have a datatype called "XML" which you can store XML in - untyped or typed with a XSD schema.
SQL Server 2005及更高版本的数据类型称为“XML”，您可以将XML存储在 - 无类型或使用XSD架构键入。
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文字字符串中填充XML类型的列，因此您可以轻松地使用普通的INSERT语句并将XML内容填充到该字段中。
Marc
渣子
#2
4
You can use the function OPENXML and stored procedure sp_xml_preparedocument to easily convert your XML into rowsets.
您可以使用函数OPENXML和存储过程sp_xml_preparedocument轻松地将XML转换为行集。
#3
2
If you are using SQL Server 2008 (or 2005), it has an xml native datatype. You can associate an XSD schema with xml variables, and Insert directly into columns of type xml.
如果您使用的是SQL Server 2008（或2005），则它具有xml本机数据类型。您可以将XSD架构与xml变量相关联，并直接插入xml类型的列中。
#4
2
Yes, SQL Server 2005 and above can parse XML out of the box.
是的，SQL Server 2005及更高版本可以开箱即用地解析XML。
You use the nodes, value and query methods to break it down how you want, whether values or attributes
您可以使用节点，值和查询方法将其分解为所需的值，无论是值还是属性
Some shameless plugging:
一些无耻的堵塞：
- Importing XML into SQL Server
- 将XML导入SQL Server
- Search XML Column in SQL
- 在SQL中搜索XML列
#5
0
Xml data and Xml document could have different meaning. When xml type is good for data, it doesn't save formatting (white spaces removed), so in some cases (e.g. cofiguration files) the best option is nvarchar.
Xml数据和Xml文档可能有不同的含义。当xml类型适用于数据时，它不保存格式（删除空格），因此在某些情况下（例如，配置文件），最佳选项是nvarchar。
#1
10
SQL Server 2005 and up have a datatype called "XML" which you can store XML in - untyped or typed with a XSD schema.
SQL Server 2005及更高版本的数据类型称为“XML”，您可以将XML存储在 - 无类型或使用XSD架构键入。
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文字字符串中填充XML类型的列，因此您可以轻松地使用普通的INSERT语句并将XML内容填充到该字段中。
Marc
渣子
#2
4
You can use the function OPENXML and stored procedure sp_xml_preparedocument to easily convert your XML into rowsets.
您可以使用函数OPENXML和存储过程sp_xml_preparedocument轻松地将XML转换为行集。
#3
2
If you are using SQL Server 2008 (or 2005), it has an xml native datatype. You can associate an XSD schema with xml variables, and Insert directly into columns of type xml.
如果您使用的是SQL Server 2008（或2005），则它具有xml本机数据类型。您可以将XSD架构与xml变量相关联，并直接插入xml类型的列中。
#4
2
Yes, SQL Server 2005 and above can parse XML out of the box.
是的，SQL Server 2005及更高版本可以开箱即用地解析XML。
You use the nodes, value and query methods to break it down how you want, whether values or attributes
您可以使用节点，值和查询方法将其分解为所需的值，无论是值还是属性
Some shameless plugging:
一些无耻的堵塞：
- Importing XML into SQL Server
- 将XML导入SQL Server
- Search XML Column in SQL
- 在SQL中搜索XML列
#5
0
Xml data and Xml document could have different meaning. When xml type is good for data, it doesn't save formatting (white spaces removed), so in some cases (e.g. cofiguration files) the best option is nvarchar.
Xml数据和Xml文档可能有不同的含义。当xml类型适用于数据时，它不保存格式（删除空格），因此在某些情况下（例如，配置文件），最佳选项是nvarchar。