I am planing to use SQL Server to store XML BLOBs for my application. I am struggling with a design decision, and looking for any guidelines or advice from someone experienced in this topic.
我计划使用SQL Server为我的应用程序存储XML BLOB。我正在努力做出设计决策，并寻找本主题中有经验的人的任何指导或建议。
The data that needs to be stored as XML has about 100 simple data points. They can easily be categorized into groups of maybe 20 data points each. In future versions of application, we plan to increase the scope of the data by adding new data points, some of which will be hierarchical (lists, dictionaries, etc).
需要存储为XML的数据包含大约100个简单数据点。它们可以很容易地分成每组20个数据点的组。在未来的应用程序版本中，我们计划通过添加新的数据点来增加数据的范围，其中一些数据点将是分层的（列表，字典等）。
We are not anticipating needing to perform queries on the XML data. At most they will be very simple queries and we can promote any of the data points to a relational column if need be.
我们预计不需要对XML数据执行查询。它们最多只是非常简单的查询，如果需要，我们可以将任何数据点提升到关系列。
I am not sure whether I should just create one giant XML BLOB to hold all of this data, or if should break it down into multiple XML columns. Are there any best practices or guidelines for dealing with XML data type in SQL Server 2008 R2 that can help me make the best decision? Does it even matter?
我不确定是否应该创建一个巨大的XML BLOB来保存所有这些数据，或者是否应该将其分解为多个XML列。在SQL Server 2008 R2中处理XML数据类型是否有任何可以帮助我做出最佳决策的最佳实践或指南？它甚至重要吗？
EDIT: I am already set on using XML as a datatype, I am trying to make a decision on whether I should use one large BLOB or break it up into multiple XML columns.
编辑：我已经开始使用XML作为数据类型，我试图决定是否应该使用一个大型BLOB或将其分解为多个XML列。
3 个解决方案
#1
6
Yes it matters! When you store a large XML blob as XML datatype inside SQL Server, then it's not stored as a textual blob - it's "parsed" and "tokenized" and stored in a significantly more efficient manner than if you're using just varchar(max) to store the textual representation.
是的，这很重要！当您将大型XML blob作为XML数据类型存储在SQL Server中时，它不会存储为文本blob - 它被“解析”和“标记化”并以比使用varchar（max）更有效的方式存储存储文本表示。
If it really looks like XML, smells like XML and quacks like XML - then definitely USE the XML datatype!
如果它看起来像XML，闻起来像XML和像XML一样的嘎嘎声 - 那么肯定使用XML数据类型！
Update: if you only intend to store and retrieve the XML as a whole - I don't see any benefit in breaking it up into chunks. The XML datatype in SQL Server can hold up to 2 GByte of data (just like varchar(max)) and you won't see any performance gains from storing (and retrieving) multiple smaller XML fragments.
更新：如果您只打算存储和检索整个XML - 我认为将其分解为块没有任何好处。 SQL Server中的XML数据类型最多可以容纳2 GB的数据（就像varchar（max）一样），并且您不会看到存储（和检索）多个较小的XML片段会带来任何性能提升。
#2
0
If the field is going to be used to store application payloads and the application can properly handle versioning or future modifications to the structure then I would go with an xml field. The only drawback to storing as xml is that queries can be more time consuming as opposed to flattened out data. If your apps handle all data being handed of as xml then this becomes a smaller hurdle to jump.
如果该字段将用于存储应用程序有效负载，并且应用程序可以正确处理版本化或将来对结构进行修改，那么我将使用xml字段。作为xml存储的唯一缺点是查询可能比耗尽数据更耗时。如果您的应用程序处理所有以xml形式传递的数据，那么这将成为一个较小的障碍。
#3
0
Yes.
是。
It is recommendable to use nvarchar(MAX) to store XML. Since, If (near future) you plan to change it modify your storage to keep json(instead of xml), then it would be highly flexible to keep Json for you. But if you chose to go for XML Datatype it is rigid to go only for XML. However, as Mark mentioned If it really looks like XML, smells like XML and quacks like XML - then definitely USE the XML datatype! And if your business use case says to insert and retrieve the whole XML at once then I don't see there is much benefit of benefit in breaking it up into chunks on performance.
建议使用nvarchar（MAX）来存储XML。因为，如果（不久的将来）你打算改变它，修改你的存储以保持json（而不是xml），那么为你保留Json会非常灵活。但是，如果您选择使用XML数据类型，那么仅针对XML是不严格的。但是，正如Mark所说，如果它看起来像XML，闻起来像XML和像XML这样的嘎嘎声 - 那么肯定会使用XML数据类型！如果您的业务用例说明要立即插入和检索整个XML，那么我没有看到将其分解为性能块的好处有很多好处。
#1
6
Yes it matters! When you store a large XML blob as XML datatype inside SQL Server, then it's not stored as a textual blob - it's "parsed" and "tokenized" and stored in a significantly more efficient manner than if you're using just varchar(max) to store the textual representation.
是的，这很重要！当您将大型XML blob作为XML数据类型存储在SQL Server中时，它不会存储为文本blob - 它被“解析”和“标记化”并以比使用varchar（max）更有效的方式存储存储文本表示。
If it really looks like XML, smells like XML and quacks like XML - then definitely USE the XML datatype!
如果它看起来像XML，闻起来像XML和像XML一样的嘎嘎声 - 那么肯定使用XML数据类型！
Update: if you only intend to store and retrieve the XML as a whole - I don't see any benefit in breaking it up into chunks. The XML datatype in SQL Server can hold up to 2 GByte of data (just like varchar(max)) and you won't see any performance gains from storing (and retrieving) multiple smaller XML fragments.
更新：如果您只打算存储和检索整个XML - 我认为将其分解为块没有任何好处。 SQL Server中的XML数据类型最多可以容纳2 GB的数据（就像varchar（max）一样），并且您不会看到存储（和检索）多个较小的XML片段会带来任何性能提升。
#2
0
If the field is going to be used to store application payloads and the application can properly handle versioning or future modifications to the structure then I would go with an xml field. The only drawback to storing as xml is that queries can be more time consuming as opposed to flattened out data. If your apps handle all data being handed of as xml then this becomes a smaller hurdle to jump.
如果该字段将用于存储应用程序有效负载，并且应用程序可以正确处理版本化或将来对结构进行修改，那么我将使用xml字段。作为xml存储的唯一缺点是查询可能比耗尽数据更耗时。如果您的应用程序处理所有以xml形式传递的数据，那么这将成为一个较小的障碍。
#3
0
Yes.
是。
It is recommendable to use nvarchar(MAX) to store XML. Since, If (near future) you plan to change it modify your storage to keep json(instead of xml), then it would be highly flexible to keep Json for you. But if you chose to go for XML Datatype it is rigid to go only for XML. However, as Mark mentioned If it really looks like XML, smells like XML and quacks like XML - then definitely USE the XML datatype! And if your business use case says to insert and retrieve the whole XML at once then I don't see there is much benefit of benefit in breaking it up into chunks on performance.
建议使用nvarchar（MAX）来存储XML。因为，如果（不久的将来）你打算改变它，修改你的存储以保持json（而不是xml），那么为你保留Json会非常灵活。但是，如果您选择使用XML数据类型，那么仅针对XML是不严格的。但是，正如Mark所说，如果它看起来像XML，闻起来像XML和像XML这样的嘎嘎声 - 那么肯定会使用XML数据类型！如果您的业务用例说明要立即插入和检索整个XML，那么我没有看到将其分解为性能块的好处有很多好处。