使用 XML 类型在 SQL Server 中实现复杂架构的建议
我的应用程序具有复杂的域实体架构。 要求使用SQL Server 2008。以下是复杂性：
域实体是分层的：数据结构是树；它嵌套到许多级别。树中很少有节点是可重复的（多值）。例如，实体可以拥有无限的地址（家庭、账单、运输、办公室等）
域实体可扩展：架构将来可能会扩展（而不是缩小）。
直接将这样的模式设计为相关的 SQL Server 表是相当具有挑战性的。如果不设计，查询也一定如此。
我正在考虑使用 XML 类型来存储域实体记录。但是我有以下疑问：
- 由于特殊的报告需求，每个字段都应该是可查询的（在实体记录内和跨实体记录）。这甚至适用于将来添加到架构中的字段。
- 当使用 XML 类型时，由于我失去了结构，我可以设计的最佳数据访问层是什么？
- 在这种情况下我可以有效地使用实体框架吗？
- 有什么推荐的最佳实践吗？
My application has a complex schema for the domain entity. It is required use SQL Server 2008. Following are the complexities:
Domain Entity is Hierarchical: The data structure is a tree; it is nested to many levels. Few nodes in the tree are repeatable (multi-valued). For example, the entity can have unlimited addresses (home, billing, shipping, office, etc.)
Domain Entity is Expandable: The schema may expand (not shrink) in future.
Designing such a schema directly as related SQL Server tables is quite challenging. If not designing, quering will surely be so.
I am thinking of using XML type to store the domain entity records. However I have following queries:
- Due to peculiar reporting needs, each field should be query-able (within and across entity records). This applies to even the fields that are added in future to the schema.
- While using XML type, since I lose the structure, what is the best Data Access Layer I can design?
- Can I use Entity Framework effectively in this situation?
- Any best practices recommended?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
一个建议是：不要这样做。严重地。你已经走下坡路了——最好学会使用数据库。
您在此处定义的“域实体”将会很大，这意味着查询它将是一个挑战。无限的地址意味着 100.000 个以上，您必须做好准备。任何愚蠢到索取 xml 文档的人都会得到一个糟糕的惊喜，服务器也是如此。
您还失去了很多工具——从 ORM 到报告工具。仅仅因为您滥用了数据库所具有的 XML 支持（计划用于存储文档，而不是充当伪数据库）。
您的疑问：
你知道，在英语中，这不是一个查询。这也是不可能的。
开始编写SQL。手工。或者开发自己的。您与人们使用 XML 的用途相去甚远，因此没有预定义的工具支持。
显然不是。
是的，学习正确使用SQL Server。这不是一个好方法。
One advice: DO NOT DO IT. Seriously. You are already down a slippery slope - etter learn to use databases.
The "Domain Entity" you define here will be large, which means that querying it will be a challenge.Unlimited addresses means 100.000 plus that you ahve to be prepared to. Anyone stupid enough to ask for the xml document will get a bad surprise, as will the server.
You also loose a lot of tooling left and right - from ORM's to reporting tools. Simply because you abuse wthe XML support the databae has (which is planned to store documents, not act as pseudo database).
Your queries:
In the english language, this is not a query, you know. It is also not possible.
Start writing SQL. By hand. Or develop your own. You are way out of what people use XML For, so no predefined tooling support.
Obviously no.
Yes, learn using SQL Server properly. This is NOT a good approach.
我正在为此开发一个抽象层：
http://rogeralsing.com/2011/02/28/linq-to- sqlxml/
代码可在 https://github.com/rogeralsing/linq-to 上找到-sqlxml
您可以从 Sql 服务器 XML 列中查询和选择/投影实体。
我们使用它来发展实体模式，同时保持旧版本完好无损。
也就是说，我们只将它用于特殊情况，并将 O/R 映射作为默认方法。
I'm working on an abstraction layer for this:
http://rogeralsing.com/2011/02/28/linq-to-sqlxml/
Code is available on https://github.com/rogeralsing/linq-to-sqlxml
You can query and select/project entities from Sql server XML columns.
We are using it for evolving entity schemas while keeping old versions intact.
That beeing said, we only use it for special cases and go O/R mapping as a default approach.
老实说，虽然我明白 @TomTom 的观点，但这取决于它是否只是一个 xml 文档。在 2008 中，您可以设置 XML 模式并将它们映射到 XML 字段。
与 TomTom 的答案相反，您可以像平常一样查询 xml 数据字段。检查以下SO答案以获取更多信息： https://stackoverflow.com/questions/966441 /xml-query-in-sql-server-2008
您可以使用实体框架（我对此的了解有点少），通过创建一些存储过程来查询数据，然后从代码中调用存储过程并进行强制转换它到 XDocument。这不是最漂亮的方法，但它应该有效。注意：可能还有另一种方法可以做到这一点，但据我对 EF 的了解，也许可以在问题中添加 EF 的标签？
我想您需要回到我们这里并说明您是否需要查询 1 个 xml 文档（在这种情况下，关系数据库可能会更好，由 @TomTom 建议）或多个文档（我将使用 SQL Server 来完成这项工作无论如何，您很可能有某种方法将这些文档链接在一起）。
XML 索引技巧可以在 此处
有关 SQL 2008 中 XML 的更多信息此处< /a>
Hth，
斯图
In all honesty, and whilst I see @TomTom 's point, but it depends whether it is just ONE xml document or not. With 2008, you can setup XML schema's and map them to an XML field.
In contrary to TomTom 's answer, you can query an xml data field like you would do normally. Check the following SO answer for more information: https://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008
You can use the entity framework (my knowledge is a bit short on this), by making some sproc's to query your data, then call the sproc from code and cast it to an XDocument. Not the prettiest way of doing it but it should work. Note: there might be another way of doing this, but that's as far as my knowledge of EF goes, perhaps add a tag for EF in the question?
I guess you need to come back to us and state whether you need to query 1 xml document (in which case an relational DB would possibly be better, suggested by @TomTom) or multiple documents (which I would use SQL Server to do the work. Chances are you'll have some way of linking these documents together anyway).
XML indexing tips can be found here
And some more info on XML in SQL 2008 here
Hth,
Stu
您尝试过 SisoDb 吗？如果您对此有任何疑问，我很乐意回答。使用 http://www.sisodb.com 上的联系表或通过 Twitter 联系我。
Did you try SisoDb? If you have any questions about it I would happily answer them. Use the contact form at http://www.sisodb.com or ping me at Twitter.